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











This post has been edited by alexviii: Nov 8 2006, 03:17 PM

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-LEFT...OUTER-JOIN.html

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