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
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
SELECT p1.* 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