两个表的SQL命令，需要主键和外键

I have table structures as follows:

我的表结构如下:

Table Person :

表人:

+-------+--------+
|  sno  |  sname |
+-------+--------+
|  1    | Bhanu  |
|  2    | Raghu  |
|  3    | Bunny  |
|  4    | Shyam  |
+-------+--------+

Table Friend :

表朋友:

+------+---------+
| sno  |  Friend |
+------+---------+
| 1    | 2       |
| 2    | 1       |
| 3    | 4       |
| 4    | 3       |
+------+---------+

Bhanu is friend of Raghu, vice versa.

Bhanu是Raghu的朋友,反之亦然。

Bunny is friend of Shyam, vice versa.

Bunny是Shyam的朋友,反之亦然。

If I give the 'Bhanu' name from person table, I should be able to get his friend name as well. Which is mentioned in Friend table with respect to his sno values from person table.

如果我从人员表中提供'Bhanu'的名字,我也应该能够得到他的朋友的名字。在Friend表中提到了关于他的人员表中的sno值。

Please do help me in performing this read operation with this kind of database.

请帮我用这种数据库执行这个读操作。

1 个解决方案

#1

You'll need to access the person table twice, and so will need aliases. I suggest this table structure first:

您需要两次访问person表,因此需要别名。我先建议这个表结构:

person (id, name)

friend (from_person_id, to_person_id)

This is much the same as your table structure, but the names are a bit clearer.

这与您的表结构非常相似,但名称更清晰一些。

Now you could have something like:

现在你可以有类似的东西:

SELECT
    p2.name
FROM
    friend
INNER JOIN person p1 ON (p1.id = friend.from_person_id)
INNER JOIN person p2 ON (p2.id = friend.to_person_id)
WHERE
    p1.name = 'Bhanu'
;

That lists all the friendships going from "from" to "to". You might additionally want to list all the friendships going the other way, in which case you could add OR p2.name = 'Bhanu'.

这列出了从“从”到“到”的所有友谊。您可能还想列出所有其他方式的友谊,在这种情况下,您可以添加OR p2.name ='Bhanu'。

#1