how to choose value in 1 column that must have 2 values in another – sql?

table:

id   lesson
11  11 A
8   11 B
4   11 A
4   11 A
2   11 A
6   11 A
5   11 A
13  11 A
11  11 B

the id 11 has both taught in classroom 11A, 11B. How to select the ids that have both values 11a,11b?

I tried this with no luck:

select id from table where lesson in '11A' and lesson in '11B' 

because it gives empty table, because it can’t be both 11a and b at the same time.

Answer

If it’s exectly two values you can make an inner join

select a.id 
from myTable a
inner join myTable b
on a.id = b.id 
and a.lesson = '11 A' and b.lesson = '11 B'