CODEWITHSUNDEEP
mysqlsqlselect
hllktlhndd
hllktlhndd

Reputation: 303

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.

Upvotes: 1

Views: 85

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Most SQL engines spport ANSI standard window functions. You can use row_number() for this:

SELECT product_id, product_group_id
FROM (SELECT p.product_id, p2g.product_group_id,
             ROW_NUMBER() OVER (PARTITION BY p2g.product_group_id ORDER BY p2g.product_group_id) as seqnum
       FROM products p LEFT JOIN
            product_to_group p2g
            ON p.product_id = p2g.product_id
     ) pg
WHERE product_group_id IS NULL or seqnum = 1

Upvotes: 1

Related Questions