Slightly complex JOIN issue with COUNT

[wpb]

Hello,

 

I have two tables in my database, one called bookinfo and one called reviews.

 

There's a column in the reviews table that references books in the bookinfo table called book_id.

 

I want to query the bookinfo table for each book_id and also count the number of reviews for each book_id in the same query.

 

At the moment I have this:

 

SELECT bookinfo.title, COUNT(reviews.review_id) AS number_reviews FROM bookinfo LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id GROUP BY reviews.book_id

 

This works, but doesn't return results for books with zero reviews (i.e. no entries in the reviews table). I was hoping that the LEFT JOIN would take care of that, but it doesn't seem to make any difference.

 

Does anyone have any suggestions about this?

 

Thanks,

 

Will

 

[mwasif]

You need to apply GROUP BY on bookinfo.book_id not on reviews.book_id.

SELECT bookinfo.title, COUNT(reviews.review_id) AS number_reviews FROM bookinfo 
LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id GROUP BY bookinfo.book_id

[wpb]

Thanks - that works. Could you explain why?

 

Also, if I add in a condition, so I'm only counting say, official reviews, it doesn't work as I expect:

 

SELECT bookinfo.title, COUNT(reviews.review_id) AS number_reviews FROM bookinfo

LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id WHERE reviews.official="Y" GROUP BY bookinfo.book_id

 

This knocks out books that don't have any official reviews, but I want it to return them, just with number_reviews=0.

 

Thanks,

 

Will

 

 

 

[mwasif]

LEFT JOIN details.

 

SELECT bookinfo.title, COUNT(reviews.review_id) AS number_reviews FROM bookinfo 
LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id GROUP BY bookinfo.book_id
HAVING number_reviews=0

[wpb]

Thanks!

 

[mwasif]

Is this what you were looking for?