Help with a JOIN

mikesgt2

SELECT
	n.*, COUNT(c.newsid) AS numcomments
FROM
	newstable AS n
	LEFT JOIN newscommentstable AS c ON n.id=c.newsid
GROUP BY
	c.newsid
ORDER BY
	n.date DESC

This query selects all news articles alomng with the number of comments each one has.

The problem is that if a news article has no comments then it is not returned at all. How can this query be changed so that it will return every row?

Lars_Berg

SELECT
n.*, COUNT(c.newsid) AS numcomments
FROM
newstable AS n
LEFT JOIN newscommentstable AS c ON n.id=c.newsid
GROUP BY
n.newsid
ORDER BY
n.date DESC

mikesgt2

The news table does not have a field called [FONT=courier new]newsid[/FONT]. Here is the schema for the two tables:

# Table structure for table 'news'

CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `date` int(11) default NULL,
  `title` varchar(200) default NULL,
  ... other fields ... ,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;


# Table structure for table 'news_comments'

CREATE TABLE `news_comments` (
  `id` int(11) NOT NULL auto_increment,
  `newsid` int(11) default NULL,
  `title` varchar(250) default NULL,
  ... other fields ... ,
  PRIMARY KEY  (`id`),
  KEY `news_comments_newsid` (`newsid`)
) TYPE=MyISAM;

Thanks for the response, i have posted this on devshed as well, you can see what has been posted there with this link.

http://forums.devshed.com/showthread.php?s=&threadid=42779

Lars_Berg

I meant the same as the other poster, that you should not group on the column in the right table.

SELECT 
n.id,n.date,n.title, COUNT(c.newsid) AS numcomments 
FROM 
newstable AS n 
LEFT JOIN newscommentstable AS c ON n.id=c.newsid 
GROUP BY 
n.id ,n.date,n.title
ORDER BY 
n.date DESC

I tested this query in another dbms without any problems and the expected result.

Mysql has this stupid extension to group by where you don't need to name all necessary fieds in the group by clause so what happens if you add them?

mikesgt2

That is interesting. I had a reply on devshed with this query that works:

SELECT n.*, IF(c.newsid, COUNT(*), 0) AS numcomments
FROM newstable AS n
LEFT JOIN newscommentstable AS c ON n.id=c.newsid
GROUP BY n.id
ORDER BY n.date DESC

Yours also works... I don't totally understand what you have done with the [FONT=courier new]GROUP BY[/FONT], could you explain? not that it matters, i am just interested