Find all C.id where none of the rows of B have is_true='Y'?

Question

There are two tables C and B.

They're connected by C.id=B.cid.

I need to find all the C.id es where:

• None of the rows corresponding to cid has is_true='Y'

I've tried various queries but can't wrap my head around any of them. This is one of my ideas.

select * from 
C inner join B 
on C.id=B.cid 
where
-- loop all rows of B using B.cid 
-- output if the row has is_true!='Y'

Answer 1

SELECT c.id
FROM c
WHERE NOT EXISTS (
    SELECT NULL
    FROM b
    WHERE b.cid = c.id
      AND is_true='Y'
    )

id
2
5

fiddle

Answer 2

Since all you're looking for is the key field, C.id aka B.cid, then you can accomplish your goal simply with a GROUP BY clause like so:

SELECT cid
FROM B
GROUP BY cid
HAVING MAX(is_true) = 'N'

Simplified example dbfiddle to demonstrate.