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

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?

>Solution :

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