用于选择不存在的记录的SQL查询

Question

当我尝试编写SQL查询来过滤某些数据时，我遇到了一些问题。基本上，我的表设计是1 ward can have many beds, and 1 bed can have many enrollments。

我的ward table has w_id as PK，bed table with b_id as PK and w_id as FK，enrollment table with e_id as PK and b_id as FK。

我现在要做的是把病床的名单和病区的详细资料放在一起，这些资料在登记表中是不存在的。我在Oracle数据库中尝试了SQL查询：

SELECT * FROM bed b
INNER JOIN ward w ON b.WARD_ID = w.ID
WHERE NOT EXISTS ( SELECT * FROM bed b2 
INNER JOIN enroll e ON e.BED_ID = b2.ID
WHERE b2.ID = b.ID );

它确实设法返回了我想要的结果。然而，当我尝试将上面的查询作为原生查询放入Spring Boot时，我得到了错误消息：

Encountered a duplicated sql alias [ID] during auto-discovery of a native-sql query; nested exception is org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [ID] during auto-discovery of a native-sql query

有什么想法吗？谢谢!

Answer 1

SELECT * FROM bed b
INNER JOIN ward w ON b.WARD_ID = w.ID

病床和病房表似乎都有名为id的列。通过执行select *操作，您隐式地包含了bed表和ward表中的所有列。因此，您将包含两个名为id的列。not exists部分让人分心。一些sql客户端将允许这样做，但hibernate更为严格。我没有一个可以立即测试的环境，但如果这是问题所在，下面这样的东西就可以解决了。

SELECT b.id, b.ward_id, w.ward_name FROM bed b
INNER JOIN ward w ON b.WARD_ID = w.ID
WHERE NOT EXISTS ( SELECT b2.id FROM bed b2 
INNER JOIN enroll e ON e.BED_ID = b2.ID
WHERE b2.ID = b.ID );

Answer 2

我不知道这是否与您的问题有关，但是您不需要在子查询中使用JOIN。一个更简单的版本是：

SELECT *
FROM bed b INNER JOIN
     ward w
     ON b.WARD_ID = w.ID
WHERE NOT EXISTS (SELECT 1 
                  FROM enroll e
                  WHERE e.BED_ID  = b.ID
                 );