Intersection on values of table (SQL)

Question

I have a table in the following manner:

---

Class | Dept | programs |        
.........................        
1     | 1    |2001      |     
.........................   
1     | 1    |2002      |     
.........................   
2     | 1    |2001      |     
.........................   
2     | 1    |2003      |     
.........................  
3     | 1    |2003      |     
.........................  
3     | 1    |2004      |     
.........................  

The question I have is, when I select select distinct programs where class in (1,2) the query will return 2001, 2002, 2003. I want to select only those programs which are common for both 1 and 2 i.e. 2001. similarly when i search for class in (2,3) it should only return 2003.

Is this possible?

Answer 1

You can try below

DEMO

select programs
from tablename t1
where class in (1,2)
group by programs
having count(distinct class) =2