mysql - 为什么我的 SQL 查询不起作用？

Question

我正在解决以下问题:

对于每个喜欢学生 B 的学生 A，如果两人不是 friend ，请找出他们是否有共同的 friend C(谁可以介绍他们!)。对于所有这样的三重奏，返回 A、B 和 C 的名称和等级。

Highschooler ( ID, name, grade ) 
English: There is a high school student with unique ID and a given first name in a certain grade. 

Friend ( ID1, ID2 ) 
English: The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123). 

Likes ( ID1, ID2 ) 
English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also 

我的查询如下:

SELECT student_a.name, student_a.grade,
       student_b.name, student_b.grade,
       student_c.name, student_c.grade
FROM Highschooler student_a
    INNER JOIN Likes
        ON (Likes.ID1 = student_a.ID)
    INNER JOIN Highschooler student_b
        ON (Likes.ID2 = student_b.ID)
    INNER JOIN Friend friend_1
        ON (friend_1.ID1 = student_a.ID)
    INNER JOIN Highschooler student_C
        ON (student_c.ID = friend_1.ID2)
    INNER JOIN Friend friend_2
        ON (student_c.ID = friend_2.ID1
            AND student_b.ID = friend_2.ID2)
WHERE student_b.ID <> friend_1.ID2

我得到以下信息:


安德鲁 10​​ Cassandra 9 加布里埃尔 9
布列塔尼 10 克里斯 10 海莉 10
奥斯汀 11 乔丹 12 安德鲁 10
奥斯汀 11 乔丹 12 凯尔 12
加布里埃尔 11 亚历克西斯 11 杰西卡 11


当实际输出应该是:


安德鲁 10​​ Cassandra 9 加布里埃尔 9
奥斯汀 11 乔丹 12 安德鲁 10
奥斯汀 11 乔丹 12 凯尔 12


我检查了我的查询很多次，但无法指出让我感到困惑的边缘情况。我知道这是一个不恰当的问题。但是，我似乎无法查明我的错误。

Answer 1

我们来看查询:由于“like”是单向关系，所以只看一个方向是准确的，但是friends是对称的，所以你要聪明点。您的查询中有 on 子句，您在其中处理 friend 关系，因为它也是不对称的。如果 a 和 c，您假设 student_a.ID = friend_1.ID1 和 student_c.ID = friend_1.ID2是 friend 。为什么你认为另一个方向是不可能的？同样，您假设 friend_2.ID1 = student_c.ID 和 friend_2.ID = student_b.ID。为什么另一个方向不可能？

最后你有这个:

WHERE student_b.ID <> friend_1.ID2

你排除了student_b.ID是第一个 friend 中的第二个 friend 的情况，但是你不排除b是第一个 friend 或者a参与的情况在第二次友谊中。

建议的解决方案:

SELECT student_a.name, student_a.grade,
       student_b.name, student_b.grade,
       student_c.name, student_c.grade
FROM Highschooler student_a
    INNER JOIN Likes
        ON (Likes.ID1 = student_a.ID)
    INNER JOIN Highschooler student_b
        ON (Likes.ID2 = student_b.ID)
    INNER JOIN Friend friend_1
        ON (friend_1.ID1 = student_a.ID) OR (friend_1.ID2 = student_a.ID)
    INNER JOIN Highschooler student_C
        ON (student_c.ID = friend_1.ID1) OR (student_c.ID = friend_1.ID2)
    INNER JOIN Friend friend_2
        ON ((student_c.ID = friend_2.ID1 OR student_c.ID = friend_2.ID2)
            AND 
            (student_b.ID = friend_2.ID1 OR student_b.ID = friend_2.ID))

请注意，上面的查询未经过测试，需要一个 where 来确保 a 与 b 不同from c 不同于a 如果一个学生可以喜欢自己或者和自己成为 friend 。