MySQL group by on multiple left joins
Posted: Tue Jul 20, 2010 6:17 am
Hi everyone
I'm trying to show the count of related records from 2 related tables. The tables use different left joins.
So the main table I'm trying to list is companies, so that's my FROM clause.
But I'd like to also show a count of the related contacts and related tags. The contacts and tags tables are joined by a company_id field.
Here's the query I'm trying to run:
[sql]SELECT `companies`.`company_id`, `companies`.`company_name`, COUNT(`contacts`.`contact_id`) AS `contact_count`,
COUNT(`tags`.`tag_id`) AS `tag_count`
FROM `companies`
LEFT JOIN `contacts` ON `companies`.`company_id`=`contacts`.`company_id`
LEFT JOIN `tags` ON `companies`.`company_id`=`tags`.`company_id`
GROUP BY `companies`.`company_id`
ORDER BY `companies`.`company_id`[/sql]
But the behaviour of this isn't what I was expecting.
contact_count is correct but only if tag_count is NULL or zero. If tag_count > 0 then contact_count equals the value of tag_count.
So I reckon I probably need to adjust my GROUP BY or add an additional GROUP BY statement.
Anyone got any ideas?
Cheers, B
I'm trying to show the count of related records from 2 related tables. The tables use different left joins.
So the main table I'm trying to list is companies, so that's my FROM clause.
But I'd like to also show a count of the related contacts and related tags. The contacts and tags tables are joined by a company_id field.
Here's the query I'm trying to run:
[sql]SELECT `companies`.`company_id`, `companies`.`company_name`, COUNT(`contacts`.`contact_id`) AS `contact_count`,
COUNT(`tags`.`tag_id`) AS `tag_count`
FROM `companies`
LEFT JOIN `contacts` ON `companies`.`company_id`=`contacts`.`company_id`
LEFT JOIN `tags` ON `companies`.`company_id`=`tags`.`company_id`
GROUP BY `companies`.`company_id`
ORDER BY `companies`.`company_id`[/sql]
But the behaviour of this isn't what I was expecting.
contact_count is correct but only if tag_count is NULL or zero. If tag_count > 0 then contact_count equals the value of tag_count.
So I reckon I probably need to adjust my GROUP BY or add an additional GROUP BY statement.
Anyone got any ideas?
Cheers, B