jack brone jack brone - 1 year ago 66
SQL Question

how to exclude out of stock in mysql

I have to display complete out of stock products in my table. Here Number 1 is In Stock and 2 is Out of stock. Here I need to fetch only Out of stock (2) of product_id, if any product_id at least one id has In stock (1) it will consider as In stock and total stock status = 2 consider as out of stock

Please check here, I have tried this query but results not coming as expected

My Sqlfiddle

Answer Source

i don't understand 100% the question, maybe this can help you.

this is the link to SQLfiddle http://sqlfiddle.com/#!9/75509d/12/0

FROM price p1
LEFT JOIN price p2
  ON p1.product_id = p2.product_id
  AND p2.stockstatus = 1
WHERE p1.stockstatus = 2
  AND p2.stockstatus IS NULL
ORDER BY p1.product_id ASC