Results 1 to 3 of 3

Thread: Query Problem

  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Unanswered: Query Problem

    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.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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

  3. #3
    Join Date
    Dec 2005
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •