How to check all stock status and mark as "Sold Out" or "In Stock"-CodePudding

I have 3 table that contain product, product_design and design_type. I'm trying to query out data that have both sold out and in stock product under specific condition where if all stock under design X is 0 then mark as 'OUT_OF_STOCK' otherwise 'IN_STOCK'.

Product Table
Product_design Table
Design_type Table

SELECT a.pid, b.did, a.p_name, a.discount_price, a.original_price, a.p_description, a.p_viewable, c.t_stock, c.t_name, 
       (CASE WHEN c.t_stock='0' THEN 'OUT_OF_STOCK' ELSE 'IN_STOCK' END) AS stock_status 
FROM product a, product_design b, design_type c 
WHERE a.pid=b.pid and c.did=b.did and a.p_viewable='Y' 
GROUP BY a.pid;

output

Based on the output above, the stock_status of pid=22 should be 'IN_STOCK' because there is design under that pid still have stocks.
All design under pid=22

I'm trying to get the correct stock status.

CodePudding user response：

I think the problem might be that it is checking the first c.t_stock it finds, since there is a t_stock for each instance of that product. Maybe try using the SUM function (sum of c.t_stock). That way if sum = 0, we are sure that all of t_stock are zero.