Query Problem

megaultraninja · #1 (**permalink**) 12-18-05, 20:31

I am having trouble formulating a query on a messageboard like database.

Basically there is a `post` table: with columns:
`post_id`, `title`, `body` etc...

and a `resonse` table with columns:
`response_id`, `post_id`, `title`, `user` etc...

Very simple... But im new to databases and SQL and my problem is...
I can't figure out how to make a query that produces all of the `post` table with the number of responses in each of the posts. I know I some how:

SELECT count(*) FROM response r WHERE r.post_id = whatever.post_id

But I can't figure out to join it with a SELECT * FROM post p like query.
I have tried for an hour or two, and can't figure it out, is it some kind of weird join I haven't thought of.
Any help would be greatly appreciated.

I am using MySQL 4.1 debian.

jfulton · #2 (**permalink**) 12-18-05, 22:46

Nested query should work.

Code:

SELECT 
     p.*
     , (SELECT COUNT(*) FROM response r WHERE r.post_id = p.post_id) as num_replies
FROM
     posts p

Zinguy · #3 (**permalink**) 12-19-05, 18:32

The most traditional SQL way is:

Code:

SELECT p.PostID
           ,COUNT(r.ResponseID)
FROM POSTS p
    LEFT JOIN RESPONSES r
        ON r.PostID = p.PostID
GROUP BY p.PostID

Just add all the columns you need from POSTS
and include them in the GROUP BY

Internet Services

Web Development

Digital Marketing

Consumer Tech