SQL Return NULL if not found in table, instead of No Result
SQL Return NULL if not found in table, instead of No Result
I have two tables that contain prices for products.
Those are product_pricing.price AS price2 and products.price AS price1.
product_pricing.price AS price2
products.price AS price1
If price2 is set, I'll use that. However if it isn't I'll stick with price1.
price2
price1
SELECT
`products`.`wo_id`,
`products`.`fty_id`,
`products`.`price` AS price1,
`product_attributes`.`fty_id`,
`product_attributes`.`cat_id`,
`product_attributes`.`design_id`,
`product_attributes`.`season_id`,
`products_u_ids`.`u_id`,
`products_u_ids`.`link_id`,
`product_designs`.`design_id`,
`product_designs`.`brand_id`,
IFNULL(`product_pricing`.`u_id`, NULL),
IFNULL(`product_pricing`.`currency`, NULL) AS price2_currency,
IFNULL(`product_pricing`.`price`, NULL) AS price2,
IFNULL(`product_pricing`.`formula_id`, NULL),
IFNULL(`product_pricing`.`vat_calculated` AS price2_vat_calculated,
IFNULL(`product_pricing`.`vat_id`, NULL),
IFNULL(`product_pricing`.`timestamp_valid`, NULL),
IFNULL(`product_price_formulas`.`formula_id`, NULL),
IFNULL(`product_price_formulas`.`formula`, NULL) AS price2_formula,
IFNULL(`global_vat_tariffs`.`vat_id`, NULL),
IFNULL(`global_vat_tariffs`.`percentage`, NULL) AS price2_vat_tariff
FROM `products`
LEFT JOIN `product_attributes`
ON `products`.`fty_id` = `product_attributes`.`fty_id`
LEFT JOIN `products_u_ids`
ON `product_attributes`.`fty_id` = `products_u_ids`.`link_id`
LEFT JOIN `product_designs`
ON `product_attributes`.`design_id` = `product_designs`.`design_id`
LEFT JOIN `product_pricing`
ON `products_u_ids`.`u_id` = `product_pricing`.`u_id`
LEFT JOIN `product_price_formulas`
ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`
LEFT JOIN `global_vat_tariffs`
ON `product_pricing`.`vat_id` = `global_vat_tariffs`.`vat_id`
INNER JOIN (
SELECT `product_pricing`.`u_id`, MAX(`timestamp_valid`) AS MaxDate
FROM `product_pricing`
WHERE `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP
GROUP BY `product_pricing`.`u_id`
) AS temp ON temp.u_id = `product_pricing`.`u_id` AND temp.MaxDate = `product_pricing`.`timestamp_valid`
WHERE `products`.`wo_id` IN ('028284')
The problem is, for products that don't have price2 the query return nothing.
price2
How do I make it so that, if there is nothing for price2 to show those fields as NULL and return all other parameters that are found?
price2
I've tried this IFNULL('', NULL) however getting a syntax error on line FROM products with the configuration from above.
IFNULL('', NULL)
FROM products
What I want is if there is nothing at a certain table to just return NULL for the value that wasn't found, and return the rest of the data. @jarlh
– Borsn
3 mins ago
1 Answer
1
Just use the coalesce()
coalesce()
select coalesce(p1.price, p2.price,'I don't have p1 nor pr2') as price
from table1 p2 join table2 p2 ...
Have you noticed all the other items that are tied to
price2. Everything with IFNULL needs price2. So this needs to be applied to all of those?– Borsn
1 min ago
price2
price2
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Add some sample table data and the expected result - as formatted text, not images. (Before you spent too much time, read stackoverflow.com/help/mcve.)
– jarlh
5 mins ago