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:
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.