0

I have 2 tables, 1 table is order and another table is order_items. Order_items contains many records for a order_id from order. I would like to query table order so that, if the orders contain certain order_items (such as product_item = 'nameProduct'). it would exclude these orders out of the result. how should I do it?

My current queries are:

select * orders where order_id in (select order_id from order_items where product_item !='nameProduct');

this query not really working because the select order_id from order_items where product_item !='nameProduct' can still select entry that has same order_id but just has a different product_item

thanks in advance!

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Zekiel
  • 31
  • 1
  • 2

1 Answers1

2
SELECT  *
FROM    orders o
WHERE   NOT EXISTS ( SELECT *
                     FROM   order_items oi
                     WHERE  product_item = 'nameProduct'
                            AND oi.order_id = o.order_id )
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Performance-wise, does it matter if you do a `SELECT *` in the inner query versus something like `SELECT 'X'` or some other dummy value? – NullUserException Aug 25 '11 at 01:07
  • 1
    @NullUserException Probably not in MySQL. Sounds like the same arguments surrounding `COUNT(*)` vs `COUNT(1)`. FWIW, I've always used `SELECT 1` in `EXISTS` sub-queries – Phil Aug 25 '11 at 01:09
  • @Null - Shouldn't do. Definitely doesn't in SQL Server [as explained here](http://stackoverflow.com/questions/1221559/count-vs-count1). The subquery might cause a problem [in MySQL though](http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-slower-than-in-sql/3417190#3417190) – Martin Smith Aug 25 '11 at 01:09