mysql - 从包含两个外键数据的表中获取所有数据

Question

表instructor:ID, name, dept_name, salary

表student:ID, name, dept_name, tot_cred

表advisor:s_ID, i_ID，包含两个表的student id和instructor id。

我需要找到所有 instructor 和 student's 的名字，其中 advisor 的 department 是 CComp.Sci

我可以在讲师来自计算机科学的地方找到所有讲师和学生的 ID。而且只有学生的名字。

但不能同时弄清楚这两个名字。

我是这样写的:

SELECT student.name  
  FROM student 
 WHERE student.ID in (SELECT advisor.s_ID 
                        FROM advisor
                           , instructor 
                       WHERE advisor.i_ID = instructor.ID 
                         and instructor.dept_name = 'Comp.Sci')

Answer 1

我认为您在这里需要的根解决方案只是三个表之间的简单连接。但是由于您需要一个包含学生和教师姓名的列表，这会使事情变得复杂。一种选择是将查找匹配学生的查询与查找匹配教师的查询结合在一起。

SELECT s.name, 'student' AS type
FROM student s
INNER JOIN advisor a
    ON s.ID = a.s_ID
INNER JOIN instructor i
    ON a.i_ID = i.ID
WHERE i.dept_name = 'CComp.Sci'
UNION ALL
SELECT DISTINCT i.name, 'instructor'
FROM student s
INNER JOIN advisor a
    ON s.ID = a.s_ID
INNER JOIN instructor i
    ON a.i_ID = i.ID
WHERE i.dept_name = 'CComp.Sci'