SQL Server tough Query

Question

I'm having an issue writing a tough query. I have the following table (as an example)

fusionId | productId | departmentId
    1    |     1     |       1
    2    |     1     |       2
    3    |     2     |       1
    4    |     3     |       2

I want a query that I can pass two departmentId's to (1,2) and for the query to return the productId only if both departmentId match the same productId

So for example if I sent the departmentId's 1 & 2 I would get the result

productId
    1

Is this possible?

Answer 1

SELECT productId 
FROM YourTable
WHERE departmentId IN (1,2)
GROUP BY productId 
HAVING COUNT(DISTINCT departmentId) = 2

Or

SELECT productId 
FROM YourTable
WHERE departmentId = 1
INTERSECT
SELECT productId 
FROM YourTable
WHERE departmentId = 2