selecting two rows for each value in a column in sql-CodePudding

I have a table like below. I need to fetch only the Accounts that has both Products'Prod1' and 'Prod2'. i need to filter out the Accounts that have one neither of the Products in them or one of the prod associated with them. For eg, though Acc3 has Prod1 asociated with it it should not be selected in the query output. How can build query for achieving this?

Account	Product
Acc1	Prod1
Acc1	Prod2
Acc1	Prod3
Acc2	Prod1
Acc2	Prod2
Acc2	Prod4
Acc3	Prod1
Acc3	Prod5
Acc3	Prod6

Output:

Account	Product
Acc1	Prod1
Acc1	Prod2
Acc2	Prod1
Acc2	Prod2

CodePudding user response：

One approach uses aggregation:

WITH cte AS (
    SELECT Account
    FROM yourTable
    WHERE Product IN ('Prod1', 'Prod2')
    GROUP BY Account
    HAVING MIN(Product) <> MAX(Product)
)

SELECT Account, Product
FROM yourTable
WHERE Product IN ('Prod1', 'Prod2') AND
      Account IN (SELECT Account FROM cte);

CodePudding user response：

Approach Using Union

SELECT Account, Product
FROM yourTable WHERE Product = 'Prod1'
UNION ALL
SELECT Account, Product
FROM yourTable WHERE Product = 'Prod2' 
ORDER BY Account