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

You will need the IN clause:

SELECT order_no, item_no
FROM line_items
WHERE item_no IN

       ( SELECT item_no FROM temp_table )  

or alternately

WHERE item_no IN ( 23,21 )

or something like that...

Good Luck
Randy

--

_/_/_/_/  _/     _/  _/      _/  _/_/_/  _/_/_/_/   _/_/_/    _/_/_/
_/        _/_/   _/   _/     _/    _/    _/    _/  _/    _/  _/    _/
_/_/_/    _/ _/  _/    _/   _/     _/    _/_/_/    _/        _/    _/
_/        _/  _/ _/     _/ _/      _/    _/        _/        _/    _/
_/_/_/_/  _/   _/_/      /_/     _/_/_/  _/         _/_/_/   _/_/_/
=====================================================================
    Randy DeWoolfson  -  ergo sum    :-)        envipco_at_cais.com
 or Ron Frederick     -  ergo ?      (8P
---------------------------------------------------------------------
#include <StandardDisclaimerOnOpinions>  // Null if file not found.
=====================================================================