php - 数据库-查询以检索另一个表中不存在的行

所以我有两张桌子互相连接

TABLE Admin_Evaluate
EvalID int primary key,
EvalStatus tinyint,
UserID int (foreign to user)

TABLE Items
ItemID int primary key,
EvalID (foreign to Admin_Evalulate)

Sample Data:

| TABLE Admin Evaluate |
 ---------------------------------------------
 EvalID | EvalStatus | UserID
   1         0           5
   2         1           5
   3         0           5
   4         1           5
   5         1           5
   6         1           5


 | TABLE Items |
 ----------------------------------------------
 ItemID  | EvalID
   1         2
   2         4

所以基本上我想检索evalid，其中evalstatus=1和userid=5不存在于表项中
结果应该是5和6
提前谢谢：）

最佳答案

您可以使用NOT EXISTS：

SELECT EvalID
FROM Admin_Evaluate AS a
WHERE EvalStatus = 1 AND UserID = 5 AND
      NOT EXISTS (SELECT 1 FROM Items AS i WHERE i.EvalID = a.EvalID)