[EMAIL PROTECTED] wrote: > > Thanks for the suggestion! I modified your query to something that works for > me (I was inaccurate with the column names before, sorry... :) > > SELECT a.article_id,a.title,a.date,a.summary,COUNT(c.id) as comments > FROM articles as a,article_comments as c > WHERE a.article_id = c.article_id > GROUP BY a.article_id,a.title,a.date,a.summary > ORDER BY a.date DESC > LIMIT 3 > > This works PERFECTLY > Except: > If an article has NO comments, it is not returned at all! > > So: When my articles table contains only 3 articles, and I make a query that > has LIMIT 3, you would think that this query would return all articles, but > it does not: Only the ones that actually has one or more comments. > > I tried this with 3 articles where 2 of them had comments: Only 2 rows > returned. When I gave one comment on the last article: 3 rows returned. > > Seems like if COUNT(c.id) returns 0, then the row is not returned at all.
That's because there's no row in the comment table to join on. You want to use a LEFT JOIN here. So the revised query might look like: SELECT a.article_id,a.title,a.date,a.summary,COUNT(c.id) AS comments FROM articles AS a LEFT JOIN article_comments AS c ON a.article_id = c.article_id GROUP BY a.article_id,a.title,a.date,a.summary ORDER BY a.date DESC LIMIT 3 -- John Klein, Database Applications Developer | Omnia Mutantur, Systems Group - Harvard Law School | Nihil Interit --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
