SQL Server Requirement

Question

I had a problem composing a hard request. I have the following table (as an example)

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

I need a request through which I can pass two departmentId to (1,2), and for the request to return productId only if both departmentId correspond to the same productId

So for example, if I sent departmentId 1 and 2, I would get the result

 productId 1 

Is it 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 

Answer 2

 Create Table Temporary ( fusionId int, productId int, departmentId int, ) insert into Temporary values (1,1,1),(2,1,2),(3,2,1),(4,3,2) Select productId from Temporary Where productId in (Select productId from Temporary where departmentId = 1) and departmentId =2 

Answer 3

 SELECT productId FROM YourTable WHERE departmentId IN (1,2) GROUP bY productId HAVING COUNT(productId) > 1