sql - Select a record that has a relationship with a number of other IDs in a junction table -

• i using sqlite 3 database.
• i have 2 tables many-to-many relationship. result, have junction table persist relationship. below representation of similar have, made data etc.

teacher table:

+----+------------+ | id |    name    | +----+------------+ | 1  | teacherone | | 2  | teachertwo | +----+------------| 

studenttable:

+----+------------+ | id |    name    | +----+------------+ | 1  | studentone | | 2  | studenttwo | +----+------------+ 

teacher_student (junction table):

+-----------+-----------+ | teacherid | studentid | +-----------+-----------+ |         1 |         1 | |         1 |         2 | |         2 |         1 | +-----------+-----------+ 

what want select teacherid has record linking both studentid 1 , studentid 2. in case, give me teacherid 1.

i have tried following sql statement:

select t_s.* teacher_student t_s studentid in (1, 2) 

and returns me records have studentid of either 1 or 2. have searched answer have been unable find has helped me far, asking last resort.

if want select teacherid has record in teacher_student table each student; can use query:

select ts.teacherid (   select teacherid, count(studentid) cnt   teacher_student   group teacherid) ts join (   select count(*) scnt    student) s on ts.cnt = s.scnt 

sql fiddle demo

---

you can use query selecting 2 specific student:

select ts.teacherid (   select teacherid, count(studentid) cnt   teacher_student   studentid in (1,2)   group teacherid) ts cnt = 2 

that remove teacher has 2 connection student.