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
x
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)