Re: SQL Join query

The only problem there is when you group the LEFT JOIN pictures that have a count of 0 return a count of 1. Unfortunately there's no single query that I figured out when I did this same project that would give accurate results. John Holmes wrote:

Mattias Jönsson wrote:

SELECT pics.*, pic_comments.*, count(*) AS num_comments FROM pics, pic_comments WHERE pics.category = 1 AND pics.id = pic_comments.pic_id GROUP BY pic_comments.pic_id;

[snip]

The problem is that i only get the the pictures that have comments, i want even the pictures where there arent no comments and the field num_comments should be 0.

Need a LEFT JOIN SELECT pics.*, pic_comments.*, count(*) AS num_comments FROM pics LEFT JOIN pic_comments on pics.id = pic_comments.pic_id WHERE pics.category = 1 GROUP BY pic_comments.pic_id