--- In firebird-support@yahoogroups.com, "james_027" wrote:

> Hi Svein Erling.
>
> That was a great solution. Do you mind explaining it to me? Iam not
> familiar with the not exist syntax thanks.

The solution James refer to ought to have been (my quick solution
yesterday could be confusing):

>SELECT <Something>
>FROM Orders o
>WHERE NOT EXISTS(
>SELECT * FROM OrderItems oi
>WHERE oi.order_id = o.id
>AND oi.Product NOT IN ('Apple','Orange','Banana'))

Well, the task was simply to return all orders that does not contain
any items not in stock (assuming the stock to be ('Apple','Orange',
'Banana')). That means that there should be no OrderItems with any
other value than the stock. We select from Orders and simply use NOT
EXISTS on OrderItems. Here, we have to link to the right order
(oi.order_id = o.id) as well as check for the products we have in
stock. Extending 'stock' to be another table, we could write:

SELECT <Something>
FROM Orders o
WHERE NOT EXISTS(
SELECT * FROM OrderItems oi
WHERE oi.order_id = o.id
AND NOT EXISTS(
SELECT * from stock s
WHERE s.product = oi.Product))

i.e. return all orders that does not contain any orderitem that is not
in stock. Admittedly, the English language is slightly easier than SQL
;o). To list all orders that do contain items not in stock, simply
remove the last NOT. EXISTS can be quite useful whether or not you
prefix it with NOT.

Set