REQUIRED QUERY :
select all memberid from query 1 having higher count than query 2
the aim is to display all memberid who were leader/organiser/helper more times than they participate in event
how do i join these two select statement and view the required output
SELECT memberID, COUNT(*) AS cnt FROM ( SELECT memberID FROM leader UNION ALL SELECT memberID FROM organiser UNION ALL SELECT memberID FROM helper ) t GROUP BY memberID;
=============
select memberid,count(*) as cnt2 from eventmember group by memberid;
=======
Advertisement
Answer
another, “all_in_one” solution
select memberid, count(*) as total_cnt, sum(case when type = 'eventmember' then 1 else 0 end) as eventmember_cnt, sum(case when type = 'eventmember' then 0 else 1 end) as other_cnt from ( select memberid, 'leader' as type from leader union all select memberid, 'organiser' as type from organiser union all select memberid, 'helper' as type from helper union all select memberid, 'eventmember' as type from eventmember ) t group by memberid having sum(case when type = 'eventmember' then 1 else 0 end) < sum(case when type = 'eventmember' then 0 else 1 end)