SQL Query to find unique column value based on values from another column-CodePudding

I have a SQL Server table like this

Schema for this table and sample insert queries to build this table:

CREATE TABLE demotable (
    Id int,
    PrivilageId int,
    RoleId int    
);

insert into demotable values (1 ,40, 101)
insert into demotable values (2 ,12, 101)
insert into demotable values (3 ,40, 102)
insert into demotable values (4 ,40, 104)
insert into demotable values (5 ,12, 128)
insert into demotable values (6 ,40, 107)
insert into demotable values (7 ,17, 103)
insert into demotable values (8 ,17, 107)
insert into demotable values (9 ,15, 138)
insert into demotable values (10 ,15, 102)
insert into demotable values (11 ,17, 104)
insert into demotable values (12 ,12, 101)

As you can see If I want to see all the RoleIDs for Privilage ID 40, I can easily get that by this query.

select * from DemoTable where PrivilageID =  40

This gives me result of 101, 102, 104, 107

But my requirement is opposite. I know that this particular combination 101, 102, 104, 107 has only one PrivilageID . I need a query where I can pass 101, 102, 104, 107 and I will get an output of 40

What I tried is: I created intersect queries like this:

select PrivilageId from DemoTable where RoleID = 101
intersect
select PrivilageId from DemoTable where RoleID = 102
intersect
select PrivilageId from DemoTable where RoleID = 104
intersect
select PrivilageId from DemoTable where RoleID = 107

This works but is there any better way?

CodePudding user response：

If I understand correctly you can try HAVING with the condition aggregate function COUNT with DISTINCT condition.

We just need to add filter RoleId in CASE WHEN expression, and the count might equal as your RoleId number.

SELECT PrivilageId 
FROM DemoTable 
GROUP BY PrivilageId 
HAVING COUNT(DISTINCT CASE WHEN RoleId IN (101, 102, 104, 107) THEN RoleId END) = 4

sqlfiddle

CodePudding user response：

There a lot of possible ways to do this. As example, you can use HAVING which has already been mentioned. Another option is using EXISTS.

SELECT DISTINCT d.privilageid FROM demotable d WHERE 
EXISTS(SELECT 1 FROM demotable WHERE roleid = 101 AND d.privilageid = privilageid) AND
EXISTS(SELECT 1 FROM demotable WHERE roleid = 102 AND d.privilageid = privilageid) AND
EXISTS(SELECT 1 FROM demotable WHERE roleid = 104 AND d.privilageid = privilageid) AND
EXISTS(SELECT 1 FROM demotable WHERE roleid = 107 AND d.privilageid = privilageid)