sql - Select rows that match several conditions -

my title question not explanatory, didn't figure out better one, if has suggestion title gladly accept it!

i'm newbie , still figuring out things...

i have table pupil_teachers 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 retrieve teachers pupils 100, 101 , 102 have in common. in case teacher id 200.

pupil 103 has teacher id 200, not part of query, want query pupils 100, 101 , 102.

so start with:

select teacher_id pupil_teachers  

i don't know go next. following not correct

select teacher_id pupil_teachers pupil_id in (100, 101, 102) 

any appreciated! thanks! , not duplicate, did try search solution issue.

here 1 way using group by , having clause

select teacher_id pupil_teachers pupil_id in (100, 101, 102) group teacher_id having count(distinct pupil_id) = 3 

another way

select teacher_id pupil_teachers group teacher_id having count(case when pupil_id  = 100 1 end) > 0      , count(case when pupil_id  = 101 1 end) > 0     , count(case when pupil_id  = 102 1 end) > 0