Return null rows if not found in where mysql-CodePudding

Imagine have a request Like :

Select product_id,category, price  FROM mytable
WHERE category IN('fish', 'chips', 'nuggets')

But no products_id has a 'chips' category. Then it will return something like:

Product_id	category	price
001	fish	1,3
001	nuggets	1,4
002	fish	4,1
002	nuggets	3,1
003	fish	1,41
003	nuggets	44,1

But I want display null for each conditions not found in where conditions:

Product_id	category	price
001	fish	1,3
001	nuggets	1,4
001	chips	NULL
002	fish	4,1
002	nuggets	3,1
002	chips	NULL
003	fish	1,41
003	nuggets	44,1
003	chips	NULL

How can I do that??

CodePudding user response：

Use a calendar table approach along with a cross join:

SELECT p.product_id, c.category, t.price
FROM (SELECT DISTINCT category FROM mytable) c
CROSS JOIN (SELECT DISTINCT product_id FROM mytable) p
LEFT JOIN mytable t
    ON t.category = c.category AND
       t.product_id = p.product_id
WHERE c.category IN ('fish', 'chips', 'nuggets')
ORDER BY p.product_id, c.category;

CodePudding user response：

You can do it with case when statement.

select product_id,category, case when category in ('fish','chips','nuggets') then price else NULL end as price FROM mytable