Sql intersection query using only joins-CodePudding

I have the following tables:

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

I need to find all qualified teachers that have both classes cname = "Math" and cname = "English".

I can only use joins since sub selects are not allowed. Here is my attempt, and I'm not sure what I'm doing wrong:

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";

from this query I'm not getting any results even when valid teachers exist

CodePudding user response：

Have you ever tried doing it like this?

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';

CodePudding user response：

You don't need to join twice with different ON conditions. Especially when you're doing INNER JOIN. You just JOIN once and add WHERE t.cname IN () like this:

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");

Here's a fiddle : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=71de58884786b1fca7128d983f553189

Update:

If you're looking to find a teacher who teaches both instead of either one, you can simply add GROUP BY and HAVING like this:

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;

CodePudding user response：

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

CodePudding user response：

Because join will filter to to only items that match on both sides you can use two joins to get the correct results

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'