SELECT - need to get row with a 0...

[rab]

I am trying to make my MySQL query to select all the columns in the news table and the number of comments for it. The query I have will only return the news rows with comments.

I know the "WHERE (news.id = comments.news_id)" is preventing it but i dont know how to just fill the column with a 0 if there is none.

Query
[code]
SELECT news.*, comments.news_id, COUNT(comments.news_id) as num_comments FROM news, comments WHERE (news.id = comments.news_id)  GROUP BY news.id
[/code]

Tables
[code]
CREATE TABLE `news` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) collate latin1_general_ci NOT NULL,
  `date` date NOT NULL,
  `user` varchar(50) collate latin1_general_ci NOT NULL,
  `news` varchar(500) collate latin1_general_ci NOT NULL,
  `category` varchar(100) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `news_id` int(11) NOT NULL,
  `user` varchar(50) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;
[/code]

[fenway]

Sounds like you need a LEFT JOIN.

[rab]

Got it. Thanks :)