.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