theMasculist theMasculist - 5 months ago 12
SQL Question

Sql two table combination and distinct only specific values

I have two tables


products
--------
product_id
1
2
3
4
5
6
7
8



product_to_group
----------------
product_id | product_group_id
1 10
2 10
3 10
4 20
5 20


SELECT p.product_id, p2g.product_group_id
FROM products p
LEFT JOIN product_to_group p2g ON (p.product_id = p2g.product_id)


The result is


product_id | product_group_id
1 10
2 10
3 10
4 20
5 20
6 NULL
7 NULL
8 NULL


The problem is here, I want to

-select all rows which product_group_id value is NULL.

-select one of row which product_group_id column has same value.

-product_id not important. It can be any of them in itself.

I want this result


product id | product_group_id
1 10
4 20
6 NULL
7 NULL
8 NULL


I have been working on it for hours and I did not get the desired results.

Answer

You can use UNION:

SELECT MIN(p.product_id), pg.product_group_id
FROM products AS p
LEFT JOIN product_to_group AS pg ON p.product_id = pg.product_id
WHERE pg.product_id IS NOT NULL
GROUP BY pg.product_group_id

UNION ALL

SELECT p.product_id, pg.product_group_id
FROM products AS p
LEFT JOIN product_to_group AS pg ON p.product_id = pg.product_id
WHERE pg.product_id IS NULL

The first part of the UNION uses grouping in order to return unique product_group_id values, whereas the second part returns all product records not being related to a product_to_group record.

Comments