SQL query return all from a table if X but not Y

Question

I have a SQL table which consists of order details.

Table Example

order_id | item_type | stock_code | price_code
----------------------------------------------
1        | S         | 0005-01    | NULL
1        | P         | NULL       | PRC-1
1        | S         | 0004       | NULL
2        | S         | 0005-02    | NULL
2        | S         | 0004       | NULL

I'm trying to return all order_id's where an order contains a stock code beginning with 0005 but not with a price code beginning with PRC

My attempt

SELECT order_id
FROM order_detail as ordDetail
WHERE EXISTS(
  SELECT * 
  FROM order_detail as ordDetail2
  WHERE ordDetail2.order_id = ordDetail.order_id
  AND stock_code LIKE '0005-%'
  AND price_code NOT LIKE 'PRC%'
)

For some reason, it seems to be ignoring the price_code NOT LIKE argument?

Expected results

order_id
--------
2
2

I'd love the order_id's to be unique too if that were possible :/

Answer 1

We can build on your attempt:

SELECT od.order_id
FROM order_detail od
WHERE od.stock_code LIKE '0005-%' AND
      NOT EXISTS (SELECT 1
                  FROM ORD_DETAIL od2
                  WHERE od2.order_id = od.order_id AND
                        od2.price_code LIKE 'PRC%'
                 );

The particular changes are:

• The condition on stock_code is in the outer query not the subquery.
• The condition for price code is NOT EXISTS rather than EXISTS -- the order should not have such a row.
• The condition in the subquery is LIKE not NOT LIKE.