mysql - 如何三重连接表以查找和查找匹配两个事物的条目？

Question

我有一个很难描述的简单问题(至少对我而言)。

考虑数据库建模类(class)的以下模式:

类(class) (cid, did, name, num, creditHours),

学生(sid、fname、lname、did)

ENROLLED_IN(eid、sid、cid)

什么查询可以找到注册 course.name=Math"和 "Science"的学生的 sid？

很抱歉，我问了一个类似的(更简单的)问题，我认为我可以解决剩下的问题，但我做不到:https://stackoverflow.com/questions/18902489/how-to-find-entries-in-database-that-meet-multiple-matches

Answer 1

正如另一页所建议的，您需要对同一个表执行两个 JOIN。但是由于您想使用名称而不是 cid，因此您可以根据注册数据加入类(class)。

SELECT DISTINCT s.sid
FROM STUDENT s
INNER JOIN ENROLLED_IN e ON e.sid = s.sid
INNER JOIN COURSE c ON c.cid = e.cid AND c.Name = 'Math'
INNER JOIN COURSE c2 ON c2.cid = e.cid AND c2.Name = 'Science'

如果你需要整个学生记录，那么......

SELECT STUDENT.*
FROM STUDENT
INNER JOIN
  (SELECT DISTINCT s.sid
   FROM STUDENT s
   INNER JOIN ENROLLED_IN e ON e.sid = s.sid
   INNER JOIN COURSE c ON c.cid = e.cid AND c.Name = 'Math'
   INNER JOIN COURSE c2 ON c2.cid = e.cid AND c2.Name = 'Science'
) t0 ON t0.sid = STUDENT.sid

EDIT 你也可以使用 GROUP BY 而不是 DISTINCT

   SELECT s.sid
   FROM STUDENT s
   INNER JOIN ENROLLED_IN e ON e.sid = s.sid
   INNER JOIN COURSE c ON c.cid = e.cid AND c.Name = 'Math'
   INNER JOIN COURSE c2 ON c2.cid = e.cid AND c2.Name = 'Science'
   GROUP BY s.sid

编辑，而不是使用两个联接，您可以使用 HAVING 子句

SELECT s.sid
FROM STUDENT s
INNER JOIN ENROLLED_IN e ON e.sid = s.sid
INNER JOIN COURSE c ON c.cid = e.cid
WHERE c.Name IN ('Math', 'Science')
GROUP BY s.sid
HAVING COUNT(*) = 2