By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PermissionAccessEntities
(`peID` int, `petID` int)
;
INSERT INTO PermissionAccessEntities
(`peID`, `petID`)
VALUES
(1, 1),
(2, 4),
(3, 1),
(4, 2),
(5, 4),
(6, 4)
;
CREATE TABLE PermissionAccessEntityGroups
(`pegID` int, `peID` int, `gID` int)
;
INSERT INTO PermissionAccessEntityGroups
(`pegID`, `peID`, `gID`)
VALUES
(1, 5, 1),
(2, 5, 2),
(3, 5, 3),
(4, 6, 2),
(5, 6, 3)
;
SELECT DISTINCT(pae.peID)
FROM PermissionAccessEntities pae
JOIN PermissionAccessEntityGroups paeg1 ON paeg1.peID = pae.peID AND paeg1.gID IN (2,3)
LEFT JOIN PermissionAccessEntityGroups paeg2 ON paeg2.peID = pae.peID AND paeg2.gID NOT IN (2,3)
WHERE pae.petID = 4 AND paeg2.peID IS NULL
| peID |
|---|
| 6 |