TalkPHP - View Single Post

Orc · 07-24-2008, 04:13 AM

Quote:

Originally Posted by delayedinsanity

How would I go about forming this into one SQL query?

I have two tables, one with my news in them, one with the comments for each news article. I want to select ALL the news items, and return another column for each row with a total count of how many rows in the comments table there are which match the news id.

So basically what I want is something akin to this:

Code:

SELECT news.id, news.uid, news.cid, news.headline, news.body, count(SELECT id FROM news_comments WHERE news_comments.nid = news.id) AS total FROM news;

Except I don't know how to phrase it in proper SQL.
-m

You could do a left join as such

sql Code:

SELECT news.id, news.uid, news.cid, news.headline, news.body, count(DISTINCT news_comments.id) AS comment_count FROM news
LEFT JOIN news_comments AS news_comments ON news_comments.nid=news.id

GROUP BY news.id

Basically what this does is grab all your columns from news table, and then does a left join clause to news_comments and assigns the nid to the news id, then you could count all the comment ids dinstinctly and then rename it to comment_count, and by using group by you could still keep it where it lists all the news by how many id's there instead of the comments id's. DISTINCT is just used so it's by itself, so if you add any more count()'s you could add DISTINCT and they would have their own counts. Sorry if this is a bad description, but I can't sum it up very well. Just take it to some knowledge

If you have multiple count()'s in select without DISTINCT, they could add onto the other count clauses, thus say if you have 2 news rows, and 1 comments rows, then it would return the comments rows as 2 instead of 1 when you really have just one comment, don't understand why to be honest.