0

This works

SELECT DISTINCT  b.b_id 
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active='yes' AND b.featured='no'

When the results should be 0 rows returned, this returns a null row with count = 0

SELECT DISTINCT  b.b_id, COUNT(c.c_id) AS count
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active='yes' AND b.featured='no'

Am I doing something wrong?

lewicki
  • 480
  • 8
  • 21
  • 2
    When you use an aggregate function (`COUNT`), you need to `GROUP BY` the non-aggregated column(s). MySQL shouldn't even allow that query, but it does which leads to unpredictable behavior... – mechanical_meat May 24 '12 at 18:46
  • ahh, ok, so i've removed the DISTINCT and appended GROUP by b.b_id at the end of the query. is that right? – lewicki May 24 '12 at 18:49

3 Answers3

3

I think you want a left join instead of an inner join since you want to return a count of 0 instead of a missing row when there is no matching c record for a given b record.

Also, you should include a group by when using an aggregate function, such as count.

SELECT
    b.b_id,
    COUNT(DISTINCT c.c_id) AS count
FROM 
    b 
    LEFT JOIN c 
        ON b.b_id=c.b_id  
        AND c.active='yes' 
WHERE b.featured='no'
GROUP BY b.b_id
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
1

Try putting DISTINCT inside COUNT():

SELECT b.b_id, COUNT(DISTINCT c.c_id) AS count
FROM b
JOIN c ON b.b_id=c.b_id  
WHERE c.active='yes' 
AND b.featured='no'
GROUP BY 1

btw, please consider formatting your SQL like this, which is more readable IMHO.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
0

I'd like to try GROUP BY instead of DISTINCT.

    SELECT b.b_id, COUNT(c.c_id) AS count
    FROM 
    b INNER JOIN c 
    ON 
    b.b_id=c.b_id  
    WHERE 
    c.active='yes' 
    AND 
    b.featured='no'
    GROUP BY b.b_id
VVLeon
  • 399
  • 3
  • 8