Number of comments

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Number of comments

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You need a left join rather than an inner join.
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

I've already tried to use a LEFT JOIN but with no luck... Could you give me a hint?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Try counting a field from the comments table.
Post Reply