Check all values in a group (Relational division)


  
  
 
 create table #t1(memid varchar(100),eventcode varchar(100))

insert into #t1 values('mem1','1stdate')
insert into #t1 values('mem1','2ndate')
insert into #t1 values('mem1','3rddate')
insert into #t1 values('mem2','1stdate')
insert into #t1 values('mem2','2ndate')
insert into #t1 values('mem2','3rddate')
insert into #t1 values('mem3','1stdate')
insert into #t1 values('mem3','2ndate')
insert into #t1 values('mem4','3rddate')



--option 1
select memid from #t1 WHERE eventcode='1stdate'
intersect
select memid from #t1 WHERE eventcode='2ndate'
intersect
select memid from #t1 WHERE eventcode='3rddate'


--option 2
select distinct memid from #t1 s 
WHERE Exists (select 1 from   #t1 t WHERE t.eventcode='1stdate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='2ndate' and s.memid=t.memid)
and Exists (select 1 from   #t1 t WHERE t.eventcode='3rddate' and s.memid=t.memid)
 

--Option 3
;with mycte as (
Select * 
,SUm(
Case 
when eventcode='1stdate' then POWER(2*1,1) --1stdate
when eventcode='2ndate' then POWER(2*1,2) --2ndate
when eventcode='3rddate' then POWER(2*1,3) --3rddate
--when eventcode='4thdate' then POWER(2*1,4) --4thdate
End) Over(Partition by memid)  bitSUM  
from #t1
)


Select memid,eventcode from mycte
WHERE (2 & bitSUM = 2 ) --1stdate
and  (4 & bitSUM = 4) --2ndate
AND (8 & bitSUM = 8) --3rddate
--AND (16 & bitSUM = 16) --4thdate








---Option 4
--Celko's relational division
--https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

--Celko solution 1
SELECT DISTINCT memid
  FROM #t1 AS t1 
  WHERE NOT EXISTS
       (SELECT *
          FROM  (Select distinct eventcode from #t1 ) t
         WHERE NOT EXISTS
               (SELECT *
                  FROM  #t1 AS t2
                 WHERE (t1.memid = t2.memid)
                   AND (t2.eventcode = t.eventcode)));

 --Celko solution 2
SELECT t1.memid
   FROM #t1 AS t1, (Select distinct eventcode from #t1 ) AS t2
  WHERE t1.eventcode = t2.eventcode
  GROUP BY t1.memid 
  HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM  (Select distinct eventcode from #t1 ) AS t );

--Celko solution 3
SELECT t1.memid
  FROM #t1 AS t1
LEFT OUTER JOIN (Select distinct eventcode from #t1 ) AS t2
       ON t1.eventcode = t2.eventcode 
  GROUP BY t1.memid
HAVING COUNT(t1.eventcode) = (SELECT COUNT(eventcode) FROM (Select distinct eventcode from #t1) t)
   AND COUNT(t2.eventcode) = (SELECT COUNT(eventcode)FROM (Select distinct eventcode from #t1) t);

   
--Celko solution 4

;with mycte as (
Select distinct eventcode from #t1
)

SELECT PS1.memid 
  FROM  #t1 AS PS1    
  LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
GROUP BY PS1.memid 
Having    COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
            AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode) FROM mycte) ;



--SELECT PS1.memid,
--CASE WHEN COUNT(PS1.eventcode) > 
--                              (SELECT COUNT(eventcode) FROM mycte )
--                              AND COUNT(H1.eventcode) = (SELECT COUNT(eventcode)FROM mycte )
--            THEN 'more than all'
--            WHEN COUNT(PS1.eventcode) = (SELECT COUNT(eventcode) FROM mycte)
--                 AND COUNT(H1.eventcode) 
--                     = (SELECT COUNT(eventcode) FROM mycte)
--            THEN 'exactly all '
--            WHEN MIN(H1.eventcode) IS NULL
--            THEN 'none'
--            ELSE 'some' END AS skill_level
--  FROM  #t1 AS PS1    LEFT OUTER JOIN     mycte AS H1   ON PS1.eventcode = H1.eventcode
--  GROUP BY PS1.memid;


drop table #t1

 

https://social.msdn.microsoft.com/Forums/en-US/e676bccf-9fa8-479d-ae5f-d48a39b1ee0a/need-select-if-all-my-values-are-present?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s