By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with tablename as (
select *
from (values (1, 1, 1),
(1, 2, 0),
(1, 3, 1),
(1, 4, 1),
(1, 5, 1),
(2, 1, 1),
(2, 2, 1),
(2, 3, 1),
(2, 4, 0),
(2, 5, 0)
) v(PID, CID, Status)
)
Select PID
from Tablename
where status = 1
group by PID
having sum(case when CID in (1) then 1 else 0 end) > 0 and
sum(case when CID in (2, 3) then 1 else 0 end) > 0 and
sum(case when CID in (4) then 1 else 0 end) > 0 and
sum(case when CID in (5) then 1 else 0 end) > 0;
| PID |
|---|
| 1 |