Re: help with a query

 Mark Loth - DBA (lothmar_at_cscoe.ac.com) wrote:
: 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
 

Man, oh, man! What an ugly problem! Here's an ugly solution, too.

select order_no
from line_items
where item_no in (*your list here**)
group by order_no
having count(distinct item_no) = *your item count here*

minus

select order_no
from line_items
group by order_no
having count(distinct item_no) != *your item count here*

I cheated by having you supply the item count. On the other hand, I handled the case of repeat entries (someone ordered two hammers) and excluded rows where someone orders your list and then some. The second select removes rows in this case.

You could use a NOT EXISTS + NOT IN to exclude orders which include items you don't mention in your list, instead of the MINUS.

Enjoy!

• Nat Brooks MIS Project Leader BBN Inc. nbrooks_at_bbn.com

usual disclaimers apply. Received on Fri Apr 21 1995 - 00:00:00 CEST