Joining Two Select Statements

sandeepck · #1 (**permalink**) 06-20-11, 02:58

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

r937 · #2 (**permalink**) 06-20-11, 04:59

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