Re: help with a query

Chuck Hamilton (chuckh_at_ix.netcom.com) 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
: >
: >
: >
 

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

Fine for a list of 2 items, but a bit nasty for a list of 20!

How about:

    SELECT o.order_no, AVG(o.cost) cost

	FROM orders o, line_items l
	WHERE l.item_no IN ( <list_of_item_codes> )
	GROUP BY o.order_no
	HAVING COUNT(*) = <length_of_list>;

or:

    SELECT o.order_no, AVG(o.cost) cost

	FROM orders o, line_items l, items i
	WHERE i.name IN ( <list_of_item_names> )
	AND l.item_no = i.item_no
	GROUP BY o.order_no
	HAVING COUNT(*) = <length_of_list>;

(NB These assume that duplicate item codes are not allowed within a single order).

--
graeme
--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.