Corbee Corbee - 1 year ago 79
MySQL Question

How do I do a subquery that displays -1 if the record is null

SELECT w.warehouse,
p.category,
p.product,
(select cr.min_stock
from critical cr
where cr.warehouse_id = w.id
and cr.product_id = p.id) as min_stock
FROM warehouse w,
product p


how do I make the min_stock display -1 if it is null.

Just to clarify, I can't use join, I need to make it in such a way that all products and warehouse would display min_stock even if no min_stock is defined.

suggested optimization would be appreciated.

Answer Source

Of course you can use JOIN. You want a CROSS JOIN, so you should be explicit about how you want to combine the two tables. You can then follow the CROSS JOIN with a LEFT JOIN:

SELECT w.warehouse, p.category, p.product, 
       coalesce(cr.min_stock, -1) as min_stock 
FROM warehouse w cross join
     product p left join
     critical c
     on cr.warehouse_id = w.id and cr.product_id = p.id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download