help with a query

Fellow dba'ers,

A project group has asked for my assistance in writing a specific query. I have been unable to find a solution yet. Here is the situation:

They have the typical order,item, and line_item tables. The order table has a row for each order placed. The item table has a row for unique product offering. The line_item table is a join of the the order and item tables; each row is an ordered item on a specific order. For example:

SQL>select * from orders;

  ORDER_NO COST
---------- ----------

        11      12.39
        44      76.55
        99      12.83

SQL> select * from items;

   ITEM_NO NAME
---------- ----------

        23 hammer
        30 pliers
        21 wrench

SQL> select * from line_items;

  ORDER_NO ITEM_NO
---------- ----------

        11         23
        11         21
        99         23

The query they would like would return an order_no given a variable list of items. For example, what is the order# that has all of the following: hammer, wrench? In this situation, order# 11 would be returned. If the list contained hammer, pliers, and wrench, no order# would be returned because no order has all three items.

Any help would be greatly appreciated,

Mark Received on Thu Apr 20 1995 - 00:00:00 CEST