Hard to describe it in the title alone. Assume we have 3 tables: one with students, one with teachers, and a third one called teaching where each student gets associated to their teachers like this:
+------+------+
| s_id | t_id |
+------+------+
| s1 | t1 |
+------+------+
| s1 | t2 |
+------+------+
| s1 | t3 |
+------+------+
| s2 | t1 |
+------+------+
| s2 | t2 |
+------+------+
| s2 | t4 |
+------+------+
In this example, student s1 has teachers t1, t2 and t3, while student s2 has teachers t1, t2, and t4.
Now the problem: given the two students s1 and s2, how many teachers do they have in common? Answer is 2 (t1 and t2).
+------+
| t1 |
+------+
| t2 |
+------+
Now I don't know how to do that with a query. Intersect didn't work and after trying an inner join of the table with itself I'm all out of ideas.
It should work with a subquery like this :
SELECT t_id FROM teaching WHERE s_id = 's1'
AND t_id IN
(SELECT t_id FROM teaching WHERE s_id = 's2');
You could also use an inner join like this (demo below is updated):
SELECT t1.t_id FROM teaching t1
INNER JOIN teaching t2 ON (t1.t_id = t2.t_id)
WHERE t1.s_id = 's1' AND t2.s_id = 's2'