Counting

[Guest]

Hello,

I have made this LEFT JOIN between two tables (articles and comments) and now I have a list of all the articles and I want to the display the number of comments a given article has, how can I do this?

Sorry if this sound newbie but I'm new. BTW I'm using PHP.

[Newsgroup_User]

.oO(An-Droide)

> I have made this LEFT JOIN between two tables (articles and comments) and now
>I have a list of all the articles and I want to the display the number of
>comments a given article has, how can I do this?

That's done with the COUNT() function and a GROUP BY clause, which
groups all comments for a given article. For more details you should
post your table structures and your current query.

Micha

[Guest]

Hey there Michael, thanks for replying, so here it is:

CREATE TABLE `aticulos` (
`id` int(10) unsigned NOT NULL auto_increment,
`date_added` datetime default NULL,
`titulo` varchar(255) NOT NULL,
`autor` varchar(255) default NULL,
`body` text NOT NULL,
`articl_image` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE `comments` (
`commentid` int(10) unsigned NOT NULL auto_increment,
`article_id` int(10) unsigned NOT NULL,
`comment` text NOT NULL,
`created_at` date default NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`commentid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;


AND this is my query: SELECT * FROM (aticulos LEFT JOIN comments ON comments.article_id=aticulos.id

[Newsgroup_User]

.oO(An-Droide)

>Hey there Michael, thanks for replying, so here it is:
>
> CREATE TABLE `aticulos` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `date_added` datetime default NULL,
> `titulo` varchar(255) NOT NULL,
> `autor` varchar(255) default NULL,
> `body` text NOT NULL,
> `articl_image` varchar(255) default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
>
> CREATE TABLE `comments` (
> `commentid` int(10) unsigned NOT NULL auto_increment,
> `article_id` int(10) unsigned NOT NULL,
> `comment` text NOT NULL,
> `created_at` date default NULL,
> `name` varchar(255) NOT NULL,
> PRIMARY KEY (`commentid`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
>
>
> AND this is my query: SELECT * FROM (aticulos LEFT JOIN comments ON
>comments.article_id=aticulos.id

A side note: In your final code you shouldn't use any SELECT *, but
explicitly name all the fields you want to fetch. There are some reasons
for that and it's just better style.

Anyway, to get the number of comments (if there are any) for all
articles try something like this:

SELECT a.id, COUNT(a.id) AS comments
FROM aticulos a
INNER JOIN comments c ON a.id = c.article_id
GROUP BY a.id

Micha

[Guest]

Thanks so much Micha...it worked, but one last question, do I have to do another query with a join to display the comment records, or it could be added as "AND" clause?

Thanks again...

[Guest]

Oh wait, it actually didn't work, since it's an INNER JOIN it's not returning all the articles and if change it to a LEFT JOIN is actually not displaying the actual amount of comments, if an article doesn't have one it displays 1 comments.

Sorry to bother again...

[Guest]

Hey, found a way! here it is if anyone has similar question:

SELECT a.id, COUNT(c.article_id) AS comments FROM aticulos a INNER JOIN comments c ON a.id = c.article_id GROUP BY a.id

If you want 0, you should count a unique field of the right table, if not you will get the left table entry. duh! 😛