How can I get rows from a table that matches all the foreign keys from another table-CodePudding

Say I have two tables role and roleApp defined like this:

create table #tempRole(roleId int);

insert into #tempRole (roleId) values (1)
insert into #tempRole (roleId) values (2)

create table #tempRoleApp(roleId int, appId int);

insert into #tempRoleApp (roleId, appId) values (1, 26)
insert into #tempRoleApp (roleId, appId) values (2, 26)
insert into #tempRoleApp (roleId, appId) values (1, 27)

So, from #tempRoleApp table, I want to get only the rows that matches all the values of the #tempRole table (1 and 2), so in this case the output needs to be 26 (as it matches both 1 and 2) but not 27 as the table does not have 2, 27).

#tempRole table is actually the output from another query so it can have arbitrary number of values.

I tried few things like:

select *
from #tempRoleApp
where roleId = ALL(select roleId FROM #tempRole)

Which does not give anything... tried few more things but not getting what I want.

CodePudding user response：

I believe this gives what you were looking for.

select tra.appId
from #tempRoleApp as tra
join #tempRole as tr on tra.roleId = tr.roleId
group by tra.appId
having count(distinct tra.roleId) = (select count(distinct roleId) from #tempRole)

It uses count distinct to get the total unique roleId's in the tempRole table and compares that with the unique count of these per appId, after confirming the roleIds match between the tables.

As you clarified in the comment, once you add another tempRole roleId, now no entry has all of the Ids so no rows are returned.