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:

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

You could do a left join as such


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.

__________________
Wax on, Wax off

Nice! I've got a lot to learn when it comes to SQL joins, I just know the very basic basics at this point... probably the next thing I should endeavour to figure out.

That query does exactly what I needed though, so again, thank you.
-m

No problem, I had to do the same thing a few days ago. xD

__________________
Wax on, Wax off