RE: simple query

Take a look at "Not exists" clauses - they should do the trick. 

Enjoy!
 
-Jackie
 
Jackie D. Brock
Database Specialist - Systems Evaluation
CableLabs(r)
858 Coal Creek Circle
Louisville, CO 80027
Email: j.brock@xxxxxxxxxxxxx
303-661-3347

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lyall Barbour
Sent: Tuesday, July 29, 2008 2: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

Other related posts: