From SQL table find the same product have same product key

Question

I have table in the following structure, I need to write SQL to find the product having multiple product keys? Can anyone please help to write the SQL?

Product	Product Key
First	10
First	1
Second	2
Second	2
Third	3
Four	3
Test	5
Test	5

Expected output would be

Product
First
Third
Four

Answer 1

This is how you get using syntax from SQL Server

SELECT Product
FROM Products
WHERE "Product Key" IN (
    SELECT "Product Key"
    FROM Products
    GROUP BY "Product Key"
    HAVING COUNT(DISTINCT Product) > 1
)
GROUP BY Product;

These are the steps:

1. You have to search product key that contain multiple product.
2. We have 1 and 3.
3. And then we get product that has product key = 1 and 3.
4. Then, we will get this output:

This is fiddle for sql server.

You can adjust it to monetdb syntax

Answer 2

You can use COUNT(DISTINCT in a HAVING, so it groups up by Product but then excludes anything which has only one ProductKey.

SELECT
  p.Product
FROM Products p
GROUP BY
  p.Product
HAVING COUNT(DISTINCT p.ProductKey) > 1;

A somewhat more efficient method, but less obvious:

HAVING MIN(p.ProductKey) <> MAX(p.ProductKey);

Answer 3

You can use the HAVING clause to include only products whose total keys equal their total unique keys:

SELECT Product
FROM Products
GROUP BY Product
HAVING COUNT(ProductKey) = COUNT(DISTINCT ProductKey);

Demo here