SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Mapping
    ([MapId] int, [GroupId] int, [EntityId] int)
;
    
INSERT INTO Mapping
    ([MapId], [GroupId], [EntityId])
VALUES
    (1, 101, 2),
    (2, 102, 1),
    (3, 102, 3),
    (4, 103, 3)
;


CREATE TABLE Entity
    ([EntityId] int)
;
    
INSERT INTO Entity
    ([EntityId])
VALUES
    (1),
    (2),
    (3),
    (4)
;
8 rows affected

with p as (select * from ( values (101) ) p(groupId) )
select e.entityId
from p
cross join entity e
left join mapping m
    on  m.entityId = e.entityId
    and m.groupId  = p.groupId
group by e.entityId
having count(m.entityId) <> ( select count(*) from p)


entityId


1

3

4


Warning: Null value is eliminated by an aggregate or other SET operation.


with p as (select * from ( values (101), (102) ) p(groupId) )
select e.entityId
from p
cross join entity e
left join mapping m
    on  m.entityId = e.entityId
    and m.groupId  = p.groupId
group by e.entityId
having count(m.entityId) <> ( select count(*) from p)


entityId


1

2

3

4


Warning: Null value is eliminated by an aggregate or other SET operation.


with p as (select * from ( values (102), (103) ) p(groupId) )
select e.entityId
from p
cross join entity e
left join mapping m
    on  m.entityId = e.entityId
    and m.groupId  = p.groupId
group by e.entityId
having count(m.entityId) <> ( select count(*) from p)


entityId


1

2

4


Warning: Null value is eliminated by an aggregate or other SET operation.