alexviii 0 Report post Posted November 5, 2006 (edited) hi everyone, I have been trying to get a script to come up with the total number of comments a news item has under it news ------- id author posted title text news_comments ---------- id nid poster posted comment And I am using this query: SELECT n.id as id, n.author as author, n.posted as date, n.title as title, n.text as text, COUNT(c.id) as num_comments FROM news as n, news_comments as c WHERE n.id = c.nid GROUP BY n.id LIMIT 10 Of course that doesnt work because it only shows news entries that have comments, Ive been playing with using 2 queries but I havent found an efficiant way of doing it. 10x Edited November 8, 2006 by alexviii (see edit history) Share this post Link to post Share on other sites
jhsmurray 0 Report post Posted November 5, 2006 If you want to see all news items (regardless of whether or not they match to the comments table), then have a look at the LEFT JOIN option in this page:http://www.wellho.net/mouth/158_MySQL-LEFTOUTER-JOIN.html Share this post Link to post Share on other sites
electron 0 Report post Posted November 6, 2006 Ya LEFT JOIN it.Well the main feature of LEFT JOIN is that if the table that is being joined is empty with regards to a particular row(here your news item) it will add the table with the same fields but with the value NULL that is treated as false of empty in PHP Share this post Link to post Share on other sites
