#1
  1. No Profile Picture
    Certified Genius
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    over there
    Posts
    77
    Rep Power
    14

    select query with count


    this may be simple but i haven't yet been able to figure it out...i have three tables invloved in this query...one for forums, one for the threads, and one for posts...the format is like this...

    forums
    forumID | forumTitle

    threads
    threadID | threadForum

    posts
    postID | postThread

    all the IDs are auto increasing integers. threadForum is a foreign key that links a certain thread to which forum it is part of and postThread is a foreign key that links it to a thread...a thread has a series of posts related to it. with the lowest postID being the first post in the thread obviously. now how do i return a recordset with the rows being each forums title, a count of how many threads there in the forum, and a count of how many posts there are in a forum? the main problem i'm getting is that i can't get an aggregate function like count() to go with a row for each forum...does this involve two subqueries somehow?? anyone got any tutorials that will help? rob
    Delenda est Carthago
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    What is the query you are trying to do?

    SELECT forum.title, count(threads.threadID), count (posts.postID) FROM (posts INNER JOIN threads ON posts.postThread = threads.threadID) INNER JOIN forum ON threads.threadForum = forum.forumID GROUP BY posts.postID, threads.threadID

    Could something like this help?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select title,
      (select count(*) 
          from threads 
         where threadforum = f.forumid ) 
    as threads,
      (select count(*) 
          from posts inner join threads
        on posts.posthread = threads.threadid
        where threads.threadforum = f.forumidid )
     as posts
      from forum f

IMN logo majestic logo threadwatch logo seochat tools logo