查询多对多关系(Query from many

查询多对多关系(Query from many-to-many relationship)

学生存储学生姓名和朋友商店之间关系的列表。

Create table Student ( id int NOT NULL AUTO_INCREMENT, name varchar(35), PRIMARY KEY (id) ); insert into Student (name) values ('John'); insert into Student (name) values ('Kelly'); insert into Student (name) values ('Mary'); Create table Friend ( id_from int NOT NULL REFERENCES Student(id), id_to int NOT NULL REFERENCES Student(id), PRIMARY KEY (id_from, id_to) ); insert into Friend (id_from,id_to) values (1, 3); insert into Friend (id_from,id_to) values (1, 2); insert into Friend (id_from,id_to) values (3, 2);

如何在MySql中查询“John”的所有朋友？ 架构在这里。

http://sqlfiddle.com/#!9/aeacd/1

Student stores a list of student name and Friend stores relationship between students.

Create table Student ( id int NOT NULL AUTO_INCREMENT, name varchar(35), PRIMARY KEY (id) ); insert into Student (name) values ('John'); insert into Student (name) values ('Kelly'); insert into Student (name) values ('Mary'); Create table Friend ( id_from int NOT NULL REFERENCES Student(id), id_to int NOT NULL REFERENCES Student(id), PRIMARY KEY (id_from, id_to) ); insert into Friend (id_from,id_to) values (1, 3); insert into Friend (id_from,id_to) values (1, 2); insert into Friend (id_from,id_to) values (3, 2);

How can I query all friends of "John", for example, in MySql? The schema is here.

http://sqlfiddle.com/#!9/aeacd/1

最满意答案

我创建了一个查询。 通常，您可以使用关系表将表与自身连接起来。 查询的作用是将Student with Friend与Student ，然后在Student.id和Friend.id_from.之间的连接表中选择名为"John"的条目作为名称Friend.id_from.

查询如下所示：

SELECT * FROM Student as s1 INNER JOIN Friend as f1 on s1.id = f1.id_from INNER JOIN Student as s2 on s2.id = f1.id_to WHERE s1.name = "John";

你可以在这里试一试：

http://sqlfiddle.com/#!9/aeacd/15

I have created a query. In general you can join tables with itself using the relation table. What the query does is join Student with Friend with Student and then selects the entries with "John" as name in the joined table between Student.id and Friend.id_from.

The query looks like this:

SELECT * FROM Student as s1 INNER JOIN Friend as f1 on s1.id = f1.id_from INNER JOIN Student as s2 on s2.id = f1.id_to WHERE s1.name = "John";

you can try it out here:

http://sqlfiddle.com/#!9/aeacd/15