Page 1 of 1

Left join, with count() driving me nuts :(

Posted: Tue Oct 25, 2005 4:16 pm
by Chris Corbyn

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 desc
w3_news

Code: 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=MyISAM
w3_news_comments

Code: 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=MyISAM
What are the tables for?

The 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 :D

Posted: Tue Oct 25, 2005 4:18 pm
by Chris Corbyn
DOH! Always the way...

I needed

Code: Select all

group by
    c.pid,
    a.id
:oops:

Been staring at it for a good 30 mins too :P

Posted: Tue Oct 25, 2005 4:37 pm
by John Cartwright
So glad we could help, Chris :lol:

Posted: Tue Oct 25, 2005 4:42 pm
by raghavan20
This seems to be the syntax for the left join...
from w3schools
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
you should think of writing the query like this..

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 a.pid = c.id  //<<==========see the change here
   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 desc

Posted: Tue Oct 25, 2005 4:55 pm
by Chris Corbyn
raghavan20 wrote:This seems to be the syntax for the left join...
from w3schools
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
you should think of writing the query like this..

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 a.pid = c.id  //<<==========see the change here
   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 desc
:? That's joining the wrong fields. It's joining foreign key in w3_news (links it to a table called w3_links) with the primary key on w3_news_comments which means nothing.

c.pid is the foreign key (aka, the primary key and table a (a.id)) so the join was correct before. It was just my grouping that was too basic ;)

thanks anyway :D

Posted: Wed Oct 26, 2005 1:45 am
by raghavan20
I have a different opinion

you have got two tables:
news referred as a
comments referred as c

for a:
id - unique id for every news item

for c:
pid - foreign key for the table referenced as 'a' and the field 'id'.

for every entry in a, you are looking for comments...
so say for a news item with a unique 45, you are looking for comments which have the pid as 45
so this would come as a.id = c.pid

oh...now i see that what I wrote was wrong as well...you can give the above condition a try.... :)