sql server - SQL needed to determine id in data set -

sql server - SQL needed to determine id in data set -

i not able arrive @ reasonable sql homecoming required id's given next info set , rules. id's returned should 1, 5 , 6.

rule1 - homecoming id if both alcohol , drug categories nowadays , alcohol <= 3 , drug = no

rule2 - homecoming id if alcohol category nowadays , alcohol <= 3

rule3 - homecoming id if drug category nowadays , drug = no

id category value 1 alcohol 0 1 drug no 2 alcohol 5 2 drug yes 3 alcohol 3 3 drug yes 4 alcohol 5 4 drug no 5 alcohol 2 6 drug no 7 alcohol 5 8 drug yes

thanks in advance.

select dt.id ( select id id @datatbl grouping id having count(*) = 1 ) x inner bring together @datatbl dt on dt.id = x.id dt.value in ('0', '1', '2', '3', 'no') union select dt1.id @datatbl dt1 inner bring together ( select dt.id ( select id id @datatbl grouping id having count(*) = 2 ) x inner bring together @datatbl dt on dt.id = x.id dt.category = 'alcohol' , dt.value in ('0', '1', '2', '3') ) x on x.id = dt1.id dt1.category = 'drug' , dt1.value in ('no')

rule1:

select * mytable t1 inner bring together mytable t2 on t1.id = t2.id , t1.category <> t2.category , t2.category = 'drug' t1.category = 'alcohol' , t1.value <= 3 , t2.value = 'no'

rule2:

select * mytable t1 left bring together mytable t2 on t1.id = t2.id , t1.category <> t2.category , t2.category = 'drug' t1.category = 'alcohol' , t1.value <= 3 , t2.category null

rule3:

select * mytable t1 right bring together mytable t2 on t1.id = t2.id , t1.category <> t2.category , t2.category = 'drug' t2.value = 'no' , t1.category null

sql-server tsql join