How should I structure query?

Question

How do I find products that have p_option_id is 1 and 11.

p_id  p_option_id 
100         1  
100         2
100         3
101         1
101         2
101         11
101         12

This should return

p_id
101

Answer 1

Try this:

SELECT p_id 
FROM tableA 
WHERE p_option_id IN (1, 11)
GROUP BY p_id
HAVING COUNT(DISTINCT p_option_id) = 2;