只使用连接的Sql交集查询

Question

我有以下表格:

Class(cID: int, cname: varchar(30))qualiedteacher (pID: int, cID: int)

我需要找到两门课都上的合格教师cname = "Math"and cname = "English"

我只能使用连接，因为不允许子选择。这是我的尝试，我不确定我做错了什么:

select  c.pID as id from qualifiedTeacher as c
inner join class as t on c.cID = t.cID and t.cname = "Math"
inner join class as a on c.cID = a.cID and a.cname = "English";

从这个查询，我没有得到任何结果，即使有效的教师存在

Answer 1

你试过这样做吗?

SELECT c.pID, c.cID as id from qualifiedTeacher as c
inner join class as t on c.cID = t.cID
inner join class as a on c.cID = a.cID
where t.cname = 'Math' AND a.cname = 'English';

Answer 2

不需要使用不同的ON条件进行两次join。尤其是当你做INNER JOIN。您只需一次JOIN，然后像这样添加WHERE t.cname IN ():

SELECT c.pID as id FROM qualifiedTeacher AS c
INNER JOIN  Class AS t ON c.cID = t.cID WHERE t.cname IN ( "Math","English");

这是一个小提琴:https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= 71 de58884786b1fca7128d983f553189

更新:

如果你想找一个同时教两门课而不是任何一门课的老师，你可以简单地把GROUP BY和HAVING加起来，像这样:

SELECT c.pID as id FROM qualifiedTeacher AS c
INNER JOIN Class AS t ON c.cID = t.cID WHERE t.cname IN ("Math","English")
GROUP BY c.pID 
HAVING COUNT(t.cname) > 1;

Answer 3

Select q.pid from qualifiedteacher as q
Inner join
Class as c
On
Q.cid = c.cid
Where c.class = “math” and “English”

Answer 4

因为join将筛选到只匹配两边的项，所以可以使用两个join来获得正确的结果

SELECT T.*
FROM Teacher as T
JOIN qualifiedTeacher qt1 on T.pID = qt1.pID and qt1.cname = 'Math'
JOIN qualifiedTeacher qt2 on T.pID = qt2.pID and qt2.cname = 'English'