Re: help with a query

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
>
>
>

Try this...

select a.*,b.item_no from line_items a, line_items b where a.item_no = '11' and b=item_no = '23' and a.order_no = b.order_no;

-- 
   ><>    Chuck Hamilton    <><
   ><> chuckh_at_ix.netcom.com <><