How-to multiple select

Hello,

I have a little problem.
I'm studying a blog program.

Given two tables : post and comment, i would like to retreve the number
of comments for each post.
In postgres the following is doable :

select id, title, body, (select count(*) from comment where post_id =
post.id) as total_comment from post

How-to do the same thing with MySQL?

If you have any idea.

Regards

Salvatore

Re: How-to multiple select

Salvatore wrote:

> Hello,
>
> I have a little problem.
> I'm studying a blog program.
>
> Given two tables : post and comment, i would like to retreve the number
> of comments for each post.
> In postgres the following is doable :
>
> select id, title, body, (select count(*) from comment where post_id =
> post.id) as total_comment from post
>
> How-to do the same thing with MySQL?
>
> If you have any idea.
>
> Regards
>
> Salvatore
>

Basic usage of a "derived table or view" in a JOIN operation.

select a.id,a.title,a.body,b.cnt
from post a,
(select id,count(*) as cnt from comment group by id ) b
where a.id = b.id ;

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Re: How-to multiple select

> select id, title, body, (select count(*) from comment where post_id =
> post.id) as total_comment from post

This query also works in MySQL >= 4.1.

Markus

Re: How-to multiple select

"Salvatore" wrote in message
news:1140378475.391763.66580@g43g2000cwa.googlegroups.com...
> select id, title, body, (select count(*) from comment where post_id =
> post.id) as total_comment from post
>
> How-to do the same thing with MySQL?

Does the following not work for you?

select p.id, p.title, p.body, count(c.post_id) as total_comment
from post as p left outer join comment as c on p.id = c.post_id
group by p.id

Regards,
Bill K.

Re: How-to multiple select

Thank you very much all of you, for your rapid responses.
I am very impressed

Regards