Page 1 of 1

MySQL group by on multiple left joins

Posted: Tue Jul 20, 2010 6:17 am
by batfastad
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

Re: MySQL group by on multiple left joins

Posted: Tue Jul 20, 2010 6:48 am
by VladSun
Try using dependent subqueries for every COUNT you need.

Code: Select all

SELECT
   t.id,
   (SELECT COUNT(...) FROM t1 WHERE t1.id = t.id) ad count1,
   (SELECT COUNT(...) FROM t2 WHERE t2.id = t.id) ad count2
FROM 
   t

Re: MySQL group by on multiple left joins

Posted: Tue Jul 20, 2010 6:49 am
by VladSun
Or use COUNT(DISTINCT column_name) in your query.

Re: MySQL group by on multiple left joins

Posted: Tue Jul 20, 2010 7:21 am
by batfastad
Aha, COUNT( DISTINCT did the trick. Thanks :lol:

I like to try and avoid sub-queries whenever possible as there's probably a performance penalty in this case, correct?