#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,568
    Rep Power
    171

    Is this possible with 1 query? Discussion about a join query with subqueries


    Hello;

    I am pretty curious to learn something from this one. Looked simple but there is something about it. Pretty basic strcuture:
    posts have thread id and threads have forum id. My goal was to get total number of posts and total number of threads for each forum. Something like this:
    FORUM----------THREADS----------POSTS
    10001--------------1--------------4
    10002--------------3--------------3
    10003--------------0--------------0

    So I first tried to find number of threads per forum which was fine, then number of posts per forum, then join them:



    Number of threads per forum:
    Code:
    SELECT forums.id         AS FORUM,
           Count(threads.id) AS THREADS
    FROM   forums
           LEFT OUTER JOIN threads
                        ON forums.id = threads.forum_id
    GROUP  BY forum
    FORUM----------THREADS
    10001--------------1
    10002--------------3
    10003--------------0





    Number of posts per forum:
    Code:
     SELECT forums.id       AS FORUM,
           Count(posts.id) AS POSTS
    FROM   forums
           LEFT OUTER JOIN threads
                        ON threads.forum_id = forums.id
           LEFT OUTER JOIN posts
                        ON posts.thread_id = threads.id
    GROUP  BY forum
    FORUM----------POSTS
    10001--------------4
    10002--------------3
    10003--------------0





    Wrong results! I believe this is due to cross join effect:
    Code:
    SELECT forums.id        AS FORUM,
           Count(posts.id)  AS POSTS,
           Count(threads.id)AS THREADS
    FROM   forums
           LEFT OUTER JOIN threads
                        ON threads.forum_id = forums.id
           LEFT OUTER JOIN posts
                        ON posts.thread_id = threads.id
    GROUP  BY forum
    FORUM----------POSTS----------THREADS
    10001--------------4--------------4
    10002--------------3--------------3
    10003--------------0--------------0

    So I tried to use subquery but still get the wrong results:
    Code:
     SELECT forums.id  AS FORUM,
           QT.threads AS THREADS,
           QP.posts   AS POSTS
    FROM   forums
           LEFT OUTER JOIN (SELECT forums.id         AS FID,
                                   threads.id        AS TID,
                                   Count(threads.id) AS THREADS
                            FROM   forums
                                   LEFT OUTER JOIN threads
                                                ON threads.forum_id = forums.id
                            GROUP  BY fid,
                                      tid) AS QT
                        ON QT.fid = forums.id
           LEFT OUTER JOIN (SELECT threads.id      AS TID,
                                   Count(posts.id) AS POSTS
                            FROM   posts
                                   LEFT OUTER JOIN threads
                                                ON threads.id = posts.thread_id
                            GROUP  BY tid) AS QP
                        ON QP.tid = QT.tid
    GROUP  BY forum
    FORUM----------THREADS----------POSTS
    10001--------------1--------------4
    10002--------------1--------------1
    10003--------------0--------------NULL

    I appreaciate any help in advance.

    Thank you
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    in your last query, why didn't you use the query from example 2 (Number of posts per forum) to get QP?

    From what I can see, you also want to join table 'QP' to the table forums, not QT.
    Joining QP with QT will only be necessary if you want the number of posts per thread.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    Wrong results! I believe this is due to cross join effect:
    no, not cross join effects

    the FROM clause as you have it is correct

    perhaps you will gain some understanding by printing out the detailed rows that the join produces, before aggregating them with GROUP BY --
    Code:
    SELECT forums.id  AS forum
         , threads.id AS thread
         , posts.id   AS post
      FROM forums
    LEFT OUTER 
      JOIN threads
        ON threads.forum_id = forums.id
    LEFT OUTER 
      JOIN posts
        ON posts.thread_id = threads.id
    ORDER
        BY forums.id 
         , threads.id
         , posts.id
    this produces --
    Code:
         
    forum  thread  post
    10001    25    215
    10001    25    216
    10001    25    218
    10001    25    219
    10002    15    201
    10002    35    222
    10002    45    230
    10003   NULL   NULL
    now, if you remember your aggregate functions, can you see why when you count the non-null values of threads and posts, you're gonna end up with the same number for both?

    can you think of any way that you can count only the unique values of threads?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,568
    Rep Power
    171
    I have a lot of reading to do
    Code:
    SELECT forums.id                 AS FORUM,
           Count(posts.id)           AS POSTS,
           Count(DISTINCT threads.id)AS THREADS
    FROM   forums
           LEFT OUTER JOIN threads
                        ON threads.forum_id = forums.id
           LEFT OUTER JOIN posts
                        ON posts.thread_id = threads.id
    GROUP  BY forum
    Thank you.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    I have a lot of reading to do
    yes, and one other thing too --

    there once was a young man who went to new york city

    he approached a police officer on the street and asked "excuse me, can you please tell me how to get to carnegie hall?"

    "sure," said the cop, "practice, practice, practice"
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,568
    Rep Power
    171
    Originally Posted by r937
    yes, and one other thing too --

    there once was a young man who went to new york city

    he approached a police officer on the street and asked "excuse me, can you please tell me how to get to carnegie hall?"

    "sure," said the cop, "practice, practice, practice"
    Haha that was funny

    I realise most SQL concepts are simple to understand but can turn to really complex situations.
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,534
    Rep Power
    1906
    Originally Posted by zxcvbnm
    Haha that was funny

    I realise most SQL concepts are simple to understand but can turn to really complex situations.
    Just like a dictionary or chess, it is all the rules and possible combination that makes the thing frustra... eh.. interesting.

IMN logo majestic logo threadwatch logo seochat tools logo