SQL select values from a column by different ids that all have this value

Question

let me explain my problem: I have a table that contains product_id's and corresponding calender_week_id's for the possible delivery calender week. Each product can be delivered in multiple weeks:

| product_id | calender_week_id |    
| 1          | 1                |    
| 1          | 2                |    
| 2          | 1                |    
| 3          | 1                |    
| 3          | 2                |

...

What i now want are the calender_week_id's that match a set of product_id's (an order). Let's say I have product_id IN (1,2,3). Now I need an SQL statement that gives me all the calender_week_id's that match all the given product_id's - so in this case = 1 because calender_week_id = 1 is an corresponding value for product_id = 1, product_id = 2 and product_id = 3.

I hope you understand my problem and can help me!

Answer 1

You need a query like the following:

SELECT calendar_week_id
FROM mytable
WHERE product_id IN (1,2,3)
GROUP BY calendar_week_id
HAVING COUNT(DISTINCT product_id) = 3;

The query returns calendar_week_id values related to all distinct product_id values specified in the WHERE clause.

Demo here