MySQL group by on multiple left joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL group by on multiple left joins

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL group by on multiple left joins

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL group by on multiple left joins

Post by VladSun »

Or use COUNT(DISTINCT column_name) in your query.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL group by on multiple left joins

Post 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?
Post Reply