RE: simple query

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour
Sent: Tuesday, July 29, 2008 4:56 PM
To: oracle-l_at_freelists.org
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 Received on Tue Jul 29 2008 - 17:51:46 CDT