How to write a query that gives the names of everyone who does not own a blue house?

Q. Write a query that gives the names of everyone who does not own a blue house.

Here is my code:

SELECT persons.name
FROM persons
JOIN houses ON (persons.id=houses.owner_id)
WHERE houses.color<>'Blue';

The two tables are shown below:

My result:

Expected result:

How do I keep “Arian” from coming in my results?

“Arian” owns two houses, one red and one blue. Because of the red house he owns, he comes up on my results. I am struggling to figure out how to exclude him.

Answer

You can use not exists:

SELECT p.name
FROM persons p
WHERE NOT EXISTS (SELECT 1
                  FROM houses h
                  WHERE h.owner_id = p.id AND
                        h.color = 'Blue'
                 )

Advertisement

Answer