MySQL Query to select from table specific rows-CodePudding

I have a table which looks has the following values:

product_id	custom_id	custom_value
1	10	A
1	9	V
2	10	B
3	3	Q

I am looking for a mysql query to get all values from product_id once and select the row which has custom_id = "10" in case it is available. Nevertheless in case custom_id = 10 is not available for a product_id I would still like to return the product_id but also only once.

So the result I am looking for is

product_id	custom_id	custom_value
1	10	A
2	10	B
3	NULL	NULL

Could please someone direct me in the right direction.

select product_id, custom_id, custom_value from table where custom_id = 10

does of course only return the values for product_id "1" and "2"

CodePudding user response：

You can select the first set of rows, then union by a distinct of all the other product id's

select product_id, custom_id, custom_value from table where custom_id = 10 
union
select distinct product_id, NULL as custom_id, NULL as custom_value where custom_id <> 10

CodePudding user response：

You can first generate a ROW_NUMBER to get the first element for each "product_id", then transform to NULL values for which "product_id" does not match your value 10, using the IF function.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY custom_id = 10 DESC) AS rn
    FROM tab
)
SELECT product_id, 
       IF(custom_id=10,    custom_id, NULL) AS custom_id, 
       IF(custom_id=10, custom_value, NULL) AS custom_value 
FROM cte
WHERE rn = 1

Check the demo here.