MySQL Query to select from table specific rows

Question

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"

Eric Yang · Accepted Answer · 2022-10-13 19:08:58Z

1

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

answered Oct 13, 2022 at 19:08

Eric Yang

2,7601 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lemon · Accepted Answer · 2022-10-13 19:12:34Z

0

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.

answered Oct 13, 2022 at 19:12

lemon

15.7k6 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL Query to select from table specific rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related