SQL查询后的连接结果不存在

Question

前几天，我问了这个帖子:SQL -返回所有不是'反之亦然'

基本设置是我有一个表屋，创建如下:

CREATE TABLE houses (
id character varying(24) NOT NULL,
bit is_nice NOT NULL
);

和一个更大的表house_listings，按如下方式创建:

CREATE TABLE house_listings(
id character varying(24) NOT NULL,
house_one character varying(24) NOT NULL,
house_two character varying(24) NOT NULL,
for_sale bit NOT NULL,
PRIMARY KEY (house_one, house_two),
FOREIGN KEY (house_one) REFERENCES houses(id),
FOREIGN KEY (house_two) REFERENCES houses(id)
);

我使用问题答案中建议的命令从两个表中提取数据:

select hl.*
from house_listings hl
where not exists (select 1
from house_listings hl2
where hl2.house_one = hl.house_two and
hl2.house_two = hl.house_one
);

我的问题是，在这之后，我留下了一个表，如:

+------------------------+-------------------+------------------------+--------------+
| id                     | house_one         | house_two              | for_sale     |
+------------------------+-------------------+------------------------+--------------+
| vW4eNAC7jgZVxWAGxH4xAR | 7777              | 12345                  | 0x00         |
+------------------------+-------------------+------------------------+--------------+

我的问题如下:是否有一种方法只能从这个查询返回值，其中house_one具有is_nice值为真?我尝试将整个查询的结果与房屋连接起来，但是SQL一直抛出语法错误，所以这是否需要在查询的早期完成，而不是稍后?

感谢

Answer 1

您应该能够将此条件添加到外部查询:

select hl.*
from house_listings hl join
houses h
on hl.house_one = h.id
where h.is_nice = 1 and
not exists (select 1
from house_listings hl2
where hl2.house_one = hl.house_two and
hl2.house_two = hl.house_one
);