Check if a column value exists in another column per group

Question

I have three variables: a group variable, var1 and var2. Per group, each value of var2 should exist in var1. I want to return all groups where this isn't the case (so per group I want to know when var2 isn't included in var1)

Example:

mygroup  var1       var2
1          1          -
1          2          1
1          3          2
1          4          -
2         23         23 
2         24         20 
2         26          -
3         30         10
3         20          -
3         10          -

So in this case, I'd want my output to be group 2 since 20 isn't included in var1 (in group 2).

Any help would be greatly appreciated.

Answer 1

Another approach to use cte and temptables:

1. Find out the var2 values that is not included in var1 for the same mygroup
2. List the mygroups and group them there var2 in the list you have found in step 1.

Try below:

create table #temp (mygroup int, var1 int, var2 int)
insert into #temp values 
(1 ,         1,          null),
(1 ,         2,         1),
(1 ,         3,          2),
(1 ,         4,          null),
(2 ,        23,         23 ),
(2 ,        24,         20 ),
(2 ,        26,          null),
(3 ,        30,         10),
(3 ,        20,          null),
(3 ,        10,          null)

;with cte as (
select t.mygroup, t.var1, t2.var2
from #temp t
inner join #temp t2 on t2.var2=t.var1 and t2.mygroup = t.mygroup
)
select var2
into #notIncludeList
from #temp 
where var2 not in (select var1 from cte)

select mygroup
from #temp
where var2 in (select var2 from #notIncludeList)
group by mygroup

This solution worked in MsSql-2014.

Answer 2

use not exists

select mygroup from table_name t1
where not exists( select 1 from table_name t2 where t1.var2=t2.var1
                and t1.mygroup=t2.mygroup)
       and t1.var2 is not null