Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: Joining Two Select Statements

    Hi,

    I have two Select statements shown below. I want to join these both and should get the proper result of No of posts and No of Comments with respect to the particular user. Can anybody please help me on this

    select
    user1.userid,
    CONCAT(user1.firstname,' ',user1.lastname) as UserName,
    count(na.activity_master_id) NoOfPosts
    from
    user user1
    left outer join network_activity na on na.created_by = user1.userid
    where na.status = 'a'
    and na.network_id = 10
    and na.activity_master_id = 9
    group by user1.userid


    select
    user1.userid,
    CONCAT(user1.firstname,' ',user1.lastname) as UserName,
    count(nc.comment_by) NoOfComments
    from
    user user1
    left outer join network_comments nc on nc.comment_by = user1.userid
    left outer join network_activity na on na.id = nc.activity_id
    where na.network_id = 10
    group by user1.userid

    Thanks in Advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT user1.userid
         , CONCAT(user1.firstname,' ',user1.lastname) AS UserName
         , COALESCE(p.NoOfPosts,0) AS NoOfPosts
         , COALESCE(c.NoOfComments,0) AS NoOfComments
      FROM user AS user1
    LEFT OUTER 
      JOIN ( SELECT created_by
                  , COUNT(*) AS NoOfPosts
               FROM network_activity 
              WHERE status = 'a'
                AND network_id = 10
                AND activity_master_id = 9
             GROUP
                 BY created_by ) AS p
        ON p.created_by = user1.userid
    LEFT OUTER 
      JOIN ( SELECT comment_by
                  , COUNT(*) AS NoOfComments
               FROM network_comments AS nc
             INNER
               JOIN network_activity AS na
                 ON na.id = nc.activity_id
                AND na.network_id = 10
             GROUP
                 BY comment_by ) AS c
        ON c.comment_by = user1.userid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2011
    Posts
    3
    Thanks for the reply and it works properly. But what if i want to display the Post and Comments count of the particular network_id related users?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sandeepck View Post
    But what if i want to display the Post and Comments count of the particular network_id related users?
    sorry, i don't understand the question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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