SELECT and count(*) in one QUERY

[lordgreg]

I have 3 tables: news, comments and types.

You can check their structure and some inserts in the file attached.

All i want to know is this:

Is it possible to gather all news info WITH the info on how many comments does every news have in one single query?

i have two queries now, and if i count for every news is another query called:

$q1 = mysql_query ("SELECT id, username, subject, text, date FROM news ORDER BY date DESC LIMIT 0, 10");

while (all news...)
{

  $q2 = mysql_query ("SELECT  count(c.id) AS num FROM comments AS c, types AS t WHERE t.type='news' AND t.id=c.typeid AND c.parentid=". $q1->id ."");

}

i hope you get the point...

now i want to know if it is possible to get above two queries so that mysql will retuirn me something like this:

id, username, subject, text, date => FROM NEWS AND
num of comments => FROM COMMENTS

->

id, username, subject, text, date, num_comments (for every news that is)

thanks and best regards all 😉

[Lars_Berg]

$q1 = mysql_query 
("SELECT n.id, n.username, n.subject, n.text, n.date , count(c.id) as num
FROM news n left outer join comments c on c.parentid = n.id 
left outer join types as t on t.type='news' AND t.id=c.typeid
group by n.id, n.username, n.subject, n.text, n.date 
ORDER BY n.date DESC LIMIT 0, 10");