How-to multiple select

[Salvatore]

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

[Michael Austin]

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
:)

[Markus Popp]

> 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

[Bill Karwin]

"Salvatore" <sa*************@wanadoo.fr> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.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.

[Salvatore]

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

Regards