MySQL - Counting rows and left join problem

Question

I have 2 tables, campaigns and campaign_codes:

campaigns: id, partner_id, status

campaign_codes: id, code, status

I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign.

I have the following SQL, but of course the WHERE statement eliminates those campaigns which have no corresponding records in campaign_codes ( i want those campaigns with zero campaign_codes as well)

SELECT 
    c.id AS campaign_id, 
    COUNT(cc.id) AS code_count
FROM 
    campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
WHERE c.partner_id = 4
AND cc.status = 0
GROUP BY c.id

Rowland Shaw · Accepted Answer · 2010-02-02 18:31:33Z

22

I'd opt for something like:

SELECT 
    c.id AS campaign_id, 
    COUNT(cc.id) AS code_count
FROM 
    campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
AND cc.status = 0 -- Having this clause in the WHERE, effectively makes this an INNER JOIN
WHERE c.partner_id = 4
GROUP BY c.id

Moving the AND to the join clause makes the join succeed or fail, crucially keeping resulting rows in where there is no matching row in the 'right' table.

If it were in the WHERE, the comparisons to NULL (where there is no campaign_code) would fail, and be eliminated from the results.

edited Feb 2, 2010 at 18:31

answered Feb 2, 2010 at 17:16

Rowland Shaw

38.2k15 gold badges104 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

k00k Over a year ago

Thanks! That did the trick. Would love an explanation or pointer to some reading on how the AND is being used.

k00k Over a year ago

What I am asking is if someone can explain or point me to a resource on placing the AND before the WHERE. Haven't used that in the past and not sure I really understand it.

Rowland Shaw Over a year ago

@k00k added a bit more detail as to "why"

k00k Over a year ago

appreciate it, ta very much! :)

Oddman Over a year ago

It doesn't need to be an additional where clause, either. You can simply do an inner join and add the "add" condition to the join. Same effect, slightly cleaner imho :)

|

D'Arcy Rittich · Accepted Answer · 2010-02-02 17:13:51Z

5

SELECT 
    c.id AS campaign_id, 
    COUNT(cc.id) AS code_count
FROM 
    campaigns c
LEFT JOIN campaign_codes cc on cc.campaign_id = c.id
    AND c.partner_id = 4
    AND cc.status = 0
GROUP BY c.id

answered Feb 2, 2010 at 17:13

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Collectives™ on Stack Overflow

MySQL - Counting rows and left join problem

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related