Re: help with a query

From: Carol McCullough <mcculc_at_maui.net>
Date: 1995/04/28
Message-ID: <3nrh2e$l1d_at_hula.maui.net>#1/1

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

>In <3n5rsf$m8p_at_nsmmfs07.cscoe.ac.com> lothmar_at_cscoe.ac.com (Mark Loth -
>DBA) writes:
>>
>>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
>>
>>
>>

Here is a query that will work with a variable number of order items. You must determine the MAXIMUM and code for that. But, it will work with one, two, on up to your maximum without any code changes.

++++++++++++++++++++++++++++++++++++++++++++

select order_no
from orders r
where exists (select 'x'

              from line_items c 
              where c.order_no = r.order_no  
                and c.item_no = to_number(&&1)
                or &&1 = -1)
  and exists (select 'x' 
              from line_items c 
              where c.order_no = r.order_no  
                and c.item_no = to_number(&&2)
                or &&2 = -1)
  and exists (select 'x' 
              from line_items c 
              where c.order_no = r.order_no 
                and c.item_no = to_number(&&3)
                or &&3 = -1)

/

+++++++++++++++++++++++++++++++++++++++++++++++

To run query: specify three parameters, using -1 as a "place-holder" when less than the maximum number of items is needed. Examples (file with above query in it is named Q_FIND.SQL::

    --to find orders with hammer and wrench::    START Q_FIND 23 21 -1
    --to find orders with hammer, pliers, and wrench:    START Q_FIND 23 21 30
    --to find orders with wrench:
   START Q_FIND 21 -1 -1 Notes:
1) The above query shows how to use from one to three incoming item numbers. You can add as many clauses as you wish just by copying the "and exists....." portion and incrementing the parameters to &&4, &&5, etc. This will determine the Maximum number of items you can specify.
2) Be sure to have an index on orders (order_no) and on order_item (order_no,item_no)
or this query takes too long to execute. 3) You MUST specify at least one parameter as a value other than -1 or you will retrieve ALL orders.
4) The "-1" can be in any order in the parameter list. Parameters "21 -1 -1" will give same results as "-1 21 -1" . Aloha!
Carol McCullough
Consultant
home page: hppt//www.maui.net/~mcculc
email: mcculc_at_maui.net Received on Fri Apr 28 1995 - 00:00:00 CEST