wpb Posted March 22, 2008 Share Posted March 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/ Share on other sites More sharing options...
mwasif Posted March 22, 2008 Share Posted March 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/#findComment-498278 Share on other sites More sharing options...
wpb Posted March 22, 2008 Author Share Posted March 22, 2008 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 Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/#findComment-498314 Share on other sites More sharing options...
mwasif Posted March 23, 2008 Share Posted March 23, 2008 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 Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/#findComment-498690 Share on other sites More sharing options...
wpb Posted March 23, 2008 Author Share Posted March 23, 2008 Thanks! Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/#findComment-498702 Share on other sites More sharing options...
mwasif Posted March 23, 2008 Share Posted March 23, 2008 Is this what you were looking for? Link to comment https://forums.phpfreaks.com/topic/97374-slightly-complex-join-issue-with-count/#findComment-498768 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.