CODEWITHSUNDEEP
sqlsql-server-2008-r2subquery
Samuel Finny
Samuel Finny

Reputation: 35

Filter two columns after Group By in SQL

I have two tables like the below query. I need to find out the setid from the table "data" which has all the records in the table "Searchlist" under one setid .

create table data(SetId int,drugid int, status varchar(1))
insert into data values (1,105,'A')
insert into data values (1,103,'I')

insert into data values (2,105,'A')
insert into data values (2,103,'A')

insert into data values (3,105,'A')

insert into data values (4,110,'A')

create table searchlist (id int, status varchar(1))
insert into searchlist values (105,'A')
insert into searchlist values (103,'I')

I have trying to do this but unable to match both the columns drugid and the status after doing group by SetId.

Kindly help.

Upvotes: 1

Views: 298

Answers (1)

Serg
Serg

Reputation: 22811

This is known as relational division. One way is

select setid
from data d 
join searchlist s on d.drugid = s.id and (d.status=s.status or coalesce(s.status,'')='')
group by setid
having count(*) = (select count(*) from searchlist)

EDITED

Modified the query. NULL or '' in searchlist matches any value.

Upvotes: 3

Related Questions