查找结合两个联接的记录

问题描述

我有三个表:

用户，房屋和房屋事件

房屋对用户具有外键(user_id)，而房屋事件对房屋具有外键(house_id)

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

您可以在此处看到架构: http://sqlfiddle.com/#!9/f08db /5 -

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 UserLEFT OUTER JOIN House u ON u.user_id = user.idWHERE u.user_id IS NULL

SELECT * FROM UserLEFT OUTER JOIN House u ON u.user_id = user.idWHERE 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.

因此，在此示例中，我将得到Lee，因为他没有房子，而我也将得到John，因为即使他有房子，但其中一栋房子都有相关的停工事件. /p>

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.

推荐答案

UNION这两个查询在一起?

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

小提琴

这篇关于查找结合两个联接的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！