Show orders where all products are available. Possible?

[BiggerBrother]

I'm not sure that this is even possible with only a single query, but if it is, hopefully someone here can set me straight.

I have the standard order, and order_products tables, linked by order_id fields. Order table holds the customer id, order date etc, and the order_products table holds the product_id, quantity and order_id.

I have been asked to find a way to produce a report on all orders where they are completable. For example, given the fact that product A,B,C and D are in stock, only return those orders which have ordered one or more of those products. If the order also includes product E, currently not in stock, then don't return that order number.

Is this possible?

Many thanks for any ideas.

BB

 

[r937]

select order_id
  from order_products
inner
  join products
    on products.product_id
     = order_products.product_id  
group
    by order_id
having count(*)
     = sum(case when products.instock > 0
                then 1 else 0 end)

r937.com | rudy.ca