Question 113194 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query to select from table specific rows

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"

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search