RE: simple query

["Mark W. Farnham" <mwf@xxxxxxxx>]

select a.cust_cd
   from your_customers_table a
minus
select c.cust_cd
   from 
   ordheader c, 
   (select ord_nbr from orddetail where b.item_cd in (<list of items>)) b
   where c.ord_nbr = b.ord_nbr;

meaning:

your list of customers minus those who have ordered the items for which you
want to see only those who have never ordered them.

Presumably your list of customers is much smaller than your order headers
table, since each customer appears there only once, right?

Good luck,

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Lyall Barbour
Sent: Tuesday, July 29, 2008 4:56 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: simple query

This is the only Oracle list i belong too, so, sorry if this is a little off
topic.

Need a query that will get all the customers who have not ordered a certain
item, ever.

Have a ORDDETAIL table that links with ORDHEADER with an ORD_NBR column.
In the ORDDETAIL table, it has a row for each item on the order.
The ORDHEADER has the CUST_CD along with the ORD_NBR.

I've tried 

SELECT a.cust_cd
FROM ordheader a, orddetail b
WHERE a.ord_nbr = b.ord_nbr
AND   b.item_cd not in (<list of items>)

but this will still bring back a record for the query if there's multiple
items on an order and the item isn't in the list.

see what i mean? 
Can someone show me the light?
Lyall

-- 
Be Yourself @ mail.com!
Choose From 200+ Email Addresses
Get a Free Account at www.mail.com

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

• References:
  • simple query
    • From: Lyall Barbour

Other related posts:

simple query

RE: simple query

RE: simple query

Re: simple query