AJT_82 AJT_82 - 1 month ago 9
SQL Question

Select rows that match several conditions

My title for my question is not that explanatory, but didn't figure out a better one, so if anyone has suggestion for title I would gladly accept it!

I'm a newbie and still figuring out things...

I have a table

pupil_teachers
like so

+------+----------+------------+
| id | pupil_id | teacher_id |
+------+----------+------------+
| 1 | 100 | 200 |
| 2 | 101 | 200 |
| 3 | 102 | 200 |
| 4 | 102 | 201 |
| 5 | 101 | 201 |
| 6 | 101 | 202 |
| 7 | 103 | 200 |
+------+----------+------------+


The query would be to retrieve all teachers that pupils 100, 101 and 102 have in common. So in this case it would only be teacher with id 200.

Pupil 103 also has teacher with id 200, but is not part of query, I only want to query pupils 100, 101 and 102.

So it would start with:

SELECT teacher_id
FROM pupil_teachers
WHERE


I really don't know where to go next. Obviously the following is not correct

SELECT teacher_id
FROM pupil_teachers
WHERE pupil_id IN (100, 101, 102)


Any help is appreciated! Thanks! And hopefully this is not a duplicate, I really did try to search for solution to my issue.

Answer

Here is one way using Group By and Having clause

SELECT teacher_id
FROM pupil_teachers
WHERE pupil_id IN (100, 101, 102)
Group by teacher_id
Having Count(Distinct pupil_id) = 3

Another way

SELECT teacher_id
FROM pupil_teachers
Group by teacher_id
Having Count(Case when pupil_id  = 100 then 1 end) > 0  
   AND Count(Case when pupil_id  = 101 then 1 end) > 0 
   AND Count(Case when pupil_id  = 102 then 1 end) > 0