mysql - 如何从具有某些条件的多对多关系中获取所有值

Question

以下是我的数据库架构，其中插入了数据:

CREATE TABLE students (`id` int, `name` varchar(9));
CREATE TABLE subjects (`id` int, `credit` int, `name` varchar(43));
CREATE TABLE student_subjects (`studentId` int, `subjectId` int);
    
INSERT INTO students (`id`, `name`) VALUES
    (1, 'student 1'),
    (2, 'student 2'),
    (3, 'student 3');

INSERT INTO subjects (`id`, `credit`, `name`) VALUES
    (1, 3, 'subject 1'),
    (2, 4, 'subject 2'),
    (3, 4, 'subject 3');

INSERT INTO student_subjects (`studentId`, `subjectId`) VALUES
    (1, 2),
    (2, 2),
    (2, 3),
    (3, 1),
    (3, 3);

如果他们有一个特定的科目，我想得到所有学生和所有科目，我想为每个学生包括所有科目。
例如，我想获取所有具有 subject 2 的学生
结果应该是这样的:


学生姓名
主题名称


学生 1
科目二

学生2
科目二

学生2
主题 3


我可以用 where过滤 students其中有 subject 2像这样

select students.name as studentName, subjects.name as subjectName from students
left join student_subjects on students.id = student_subjects.studentId
left join subjects on subjects.id = student_subjects.subjectId
where subjects.id = 2

但它会过滤其他主题的数据
有什么想法可以这样做吗？谢谢

Answer 1

SELECT st1.name student, su2.name subject
-- get needed subject from this table
FROM subjects su1
-- get students list from this table
JOIN student_subjects ss1 ON su1.id = ss1.subjectId
-- get all subjects for these students
JOIN student_subjects ss2 ON ss2.studentId = ss1.studentId
-- get these subjects names from this table
JOIN subjects su2 ON su2.id = ss2.subjectId
-- get these students names from this table
JOIN students st1 ON st1.id = ss1.studentId
-- specify needed subject
WHERE su1.name = @subj

fiddle

is there is a way to drive the query from students table, I mean the form part like this from students – Imad Jomar

内部联接对源表的排序不敏感(除了在任何联接条件中使用任何列之前必须提及该表这一事实)。您可以使用

SELECT st1.name student, su2.name subject
FROM students st1 
JOIN subjects su1 
JOIN student_subjects ss1 
JOIN student_subjects ss2 
JOIN subjects su2 ON st1.id = ss1.studentId
                 AND su1.id = ss1.subjectId
                 AND ss2.studentId = ss1.studentId
                 AND su2.id = ss2.subjectId
WHERE su1.name = @subj

在这种形式中，表格的顺序没有任何意义。
或者您可以分别为每个连接表指定连接条件:

SELECT st1.name student, su2.name subject
FROM students st1 
JOIN student_subjects ss1 ON st1.id = ss1.studentId
JOIN subjects su1 ON su1.id = ss1.subjectId
JOIN student_subjects ss2 ON ss2.studentId = ss1.studentId
JOIN subjects su2 ON su2.id = ss2.subjectId
WHERE su1.name = @subj