sql - Find records combining two joins

Question

I have three tables:

User, House and HouseEvent

A House has a foreign_key (user_id) to User and an HouseEvent has a foreign_key (house_id) to House

You can see the schema here: http://sqlfiddle.com/#!9/f08db/5 -

I know how I can get all the users which do not have a house:

SELECT * FROM User LEFT OUTER JOIN House u ON u.user_id = user.id WHERE u.user_id IS NULL

But how could I get in a single query, all the users who do not have a house and those users who have a house with at (least) a suspended event.

So, in the example, I would get Lee because he does not have a house, and I would also get John, because even though he has houses, one of its houses has an associated suspended event.

Answer 1

UNION the 2 queries together?

SELECT u.id, u.name  FROM User u
JOIN House h ON h.user_id = u.id
JOIN HouseEvent he ON he.house_id = h.id AND he.name = 'suspended'
UNION
SELECT u.id, u.name FROM user u
LEFT JOIN house h ON h.user_id = u.id
WHERE h.user_id IS NULL

Fiddle