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