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

user_3938343 0
Viewed 930

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 :/

tsql sql-server sql
4 Answers

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.
user_11440350

You could use aggregation and a having clause with conditional expressions:

select order_id
from order_detail
group by order_id
having 
    max(case when stock_code like '0005-%' then 1 end) = 1
    and max(case when price_code like 'PRC%' then 1 end) is null

This will give you all (unique) ids for which (at least) a record exists where stock_code = '0005' and no record exists where price_code starts with 'PRC'.

user_106767160

You need to check for NULL values on price_code, as Larnu pointed out.

Just replace

AND price_code NOT LIKE 'PRC%'

for

AND (price_code NOT LIKE 'PRC%' OR price_code IS NULL)

You can see in the following example how the NULL doesn't match against a LIKE pattern.

SELECT 'Yes!' WHERE 'PRC-1' LIKE 'PRC%'

SELECT 'No T.T!' WHERE NULL LIKE 'PRC%' -- No match
user_46082040

Just Try the below, It works .

SELECT DISTINCT order_id FROM order_detail WHERE stock_code LIKE '0005-%' AND price_code NOT LIKE 'PRC%'

user_121232590
Related