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

I am working on the following problem:

我正在解决以下问题：

For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C.

对于每个喜欢学生B的学生A，他们两个不是朋友，找到他们是否有共同的朋友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

My query is below:

我的查询如下：

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

I get the following:

我得到以下内容：

Andrew 10 Cassandra 9 Gabriel 9 Brittany 10 Kris 10 Haley 10 Austin 11 Jordan 12 Andrew 10 Austin 11 Jordan 12 Kyle 12 Gabriel 11 Alexis 11 Jessica 11

Andrew 10 Cassandra 9 Gabriel 9 Brittany 10 Kris 10 Haley 10 Austin 11 Jordan 12 Andrew 10 Austin 11 Jordan 12 Kyle 12 Gabriel 11 Alexis 11 Jessica 11

When the actual output should be:

当实际输出应该是：

Andrew 10 Cassandra 9 Gabriel 9 Austin 11 Jordan 12 Andrew 10 Austin 11 Jordan 12 Kyle 12

Andrew 10 Cassandra 9 Gabriel 9 Austin 11 Jordan 12 Andrew 10 Austin 11 Jordan 12 Kyle 12

I reviewed my query a ton of times, yet can't point out the edge cases that are messing me up. I know this sort of an ill-posed question. However, I can't seem to pinpoint my error.

我查了很多次查询，但却无法指出让我搞砸的边缘情况。我知道这是一个不合时宜的问题。但是，我似乎无法查明我的错误。

1 个解决方案

#1

Let's see the query: Since "like" is a unidirectional relation, it is accurate to see only a single direction, but since friends is symmetrical, you need to be clever. You are having on clauses in your query, where you handle the friends relation as it was assymetric as well. You assume that student_a.ID = friend_1.ID1 and student_c.ID = friend_1.ID2 if a and c are friends. Why do you consider the other direction impossible? Similarly, you assume that friend_2.ID1 = student_c.ID and friend_2.ID = student_b.ID. Why is the other direction impossible?

让我们看看查询：由于“like”是一个单向关系，所以只能看到一个方向是准确的，但由于朋友是对称的，你需要聪明。你在查询中有条款，你在那里处理朋友关系，因为它也是不对称的。如果a和c是朋友，则假设student_a.ID = friend_1.ID1和student_c.ID = friend_1.ID2。为什么你认为其他方向不可能？同样，您假设friend_2.ID1 = student_c.ID和friend_2.ID = student_b.ID。为什么另一个方向不可能？

At the end you have this:

最后你有这个：

WHERE student_b.ID <> friend_1.ID2

Where you exclude the case that student_b.ID is the second friend in the first friendship, but you do not exclude that b is the first friend or that a participates in the second friendship.

如果你排除student_b.ID是第一个友谊中的第二个朋友的情况，但你不排除b是第一个朋友或参与第二个友谊。

#1