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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

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

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

So glad we could help, Chris :lol:
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.... :)
Post Reply