I have a table with some ice cream order data like so:
id order_number item_name
1 1 cake cone
2 1 sprinkles
3 2 sugar cone
4 3 semi-sweet morsels
5 3 sprinkles
(This is simpler than the real table but the relevant columns are present.)
Essentially what I want to do is generate a list of order numbers that don't contain particular items (sugar cones and cake cones).
What I've tried at this point seems to work but I wonder if there is a better way:
select a.order_number from
(select order_number from orders where (item_name != 'sugar cone' and item_name != 'cake cone') group by order_number)a
left join
(select order_number from orders where (item_name = 'sugar cone' or item_name = 'cake cone') group by order_number)b
on a.order_number = b.order_number
where b.order_number is null;
This will return me the order_numbers I expect (in this case, only order 3) but it seems clunky to me.
Do you have a better way to do this?