Left join, with count() driving me nuts :(
Posted: Tue Oct 25, 2005 4:16 pm
Code: Select all
select
a.id,
a.title,
a.summary,
date_format(a.date_in, '%a %D %b %Y') as date,
b.username,
count(c.id) as total
from
w3_news as a,
w3_users as b
left join
(w3_news_comments as c) on c.pid = a.id
and c.state = '1'
where
a.userid = b.id
and a.pid = '{$this->NewsID}'
and a.state = '1'
group by
c.pid
order by
a.date_in descCode: Select all
CREATE TABLE `w3_news` (
`id` int(11) NOT NULL auto_increment,
`pid` int(11) default NULL,
`title` varchar(255) default NULL,
`subtitle` varchar(255) default NULL,
`summary` text,
`body` text,
`userid` int(11) default NULL,
`date_in` date default NULL,
`state` enum('0','1') default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAMCode: Select all
CREATE TABLE `w3_news_comments` (
`id` int(11) NOT NULL auto_increment,
`pid` int(11) default NULL,
`title` varchar(255) default NULL,
`body` text,
`userid` int(11) default NULL,
`date_in` date default NULL,
`state` enum('0','1') default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAMThe first one (w3_news) stores news articles. The second (w3_news_comments) stores comments left by users in relation to the article.
w3_news_comments.pid is the id of the original article so the join can be done. If no comments have been left, there are inevitably no records with that pid (parent ID) in the w3_news_comments table so i need a left join.
What I want to pull out is:
The Title of the article
The summary
The date
The author (userid)
The total number of comments left!!!
That query seems to work OK for one record but when it only ever seems to pull out one row even if there are hundreds of rows in the database??
Any clues?
Thanks