SQL不存在(人们讨厌他们认识的每个人)已编辑

Question

所以我有这三个表

Persons {id, name}
    {1, "Jim"}
    {2, "Kim"}
    {3, "Tim"}
    {4, "Brim"}
Knows {id_A, id_B}
    {1,2}
    {1,3}
    {1,4}
    {2,3}
    {4,2}
Hates {id_A, id_B}
    {1,4}
    {2,1}
    {3,1}
    {3,2}
    {4,2}

我想使用NOT EXIST获取数据，以获取所有讨厌他们认识的人的名字。我尝试了这个查询：

SELECT DISTINCT P.name FROM Persons P, Likes L, Knows K
WHERE K.personA_id = P.id AND L.personA_id = P.id
AND NOT EXISTS
(SELECT * FROM Persons P WHERE L.personA_id = P.id AND L.personB_id <>  K.personB_id)

但如果一个人认识几个人但至少恨其中一个人，它也会返回name（例如，此查询返回{1，"Jim"}，即使他认识3个人但只恨其中一人）。我需要找一个讨厌他们认识的每一个人的人。。帮助

Answer 1

用另一种方式写，给我所有认识他们不讨厌的人的人：

SELECT * FROM Persons p
WHERE EXISTS
(
    SELECT 0 FROM Knows k
    WHERE k.personA_id = P.id
    AND NOT EXISTS
    (
        SELECT 0 FROM Hates h
        WHERE k.personA_id = h.personA_id
    )
)

Answer 2

您没有在子查询中使用其他表。你应该这样做：-

SELECT DISTINCT P.name FROM Persons P, Hates H, Knows K
WHERE K.A_id = P.id AND H.A_id = P.id
AND NOT EXISTS
(SELECT * FROM Persons P, Hates H, Knows K WHERE H.A_id = P.id and H.B_id <> K.B_id)

Answer 3

我不喜欢NOT EXISTS，但这是"讨厌他们认识的每个人的人"的可能解决方案

SELECT P.name FROM Persons p
WHERE NOT EXISTS 
(
    -- person who knows persons and doesn't hate them
  SELECT * FROM Knows k
   LEFT OUTER JOIN Hates h
   ON (h.id_B = k.id_B and h.id_A = k.id_A)
    WHERE p.id = k.id_A -- join Person
    AND  k.id_B is null
)