Page 1 of 1

Number of comments

Posted: Sun Feb 18, 2007 11:13 am
by MarK (CZ)
Hi all,

I have the following problem:
- there's one table with news,
- and a second table with comments (related to the news table by 'news_id' column)

Now, I would like to know, whether there's some possibility to get a list of news with a number of comments for each, just through one query?
I've tried this:

Code: Select all

SELECT *, COUNT(*) as comments_c 
FROM news, comments
WHERE comments.object_id = news.id 
GROUP BY news.id 
ORDER BY news.time DESC 
LIMIT 0, 5
This does well what I need but just if there are already some comments present (if not, no row is returned because condition result is FALSE).

So, is there some other way how to do this just in one query or do I have to go through every row of the result and do a query for each?

Thanks

Posted: Sun Feb 18, 2007 11:20 am
by onion2k
You need a left join rather than an inner join.

Posted: Sun Feb 18, 2007 11:26 am
by MarK (CZ)
I've already tried to use a LEFT JOIN but with no luck... Could you give me a hint?

Posted: Sun Feb 18, 2007 11:47 am
by feyd
Try counting a field from the comments table.