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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php