Re: simple query
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: lyallbarbour@xxxxxxxxxxxxxxx
- Date: Tue, 29 Jul 2008 16:04:16 -0700
On Tue, Jul 29, 2008 at 1:56 PM, Lyall Barbour <lyallbarbour@xxxxxxxxxxxxxxx
> wrote:
> 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>)
>
>
Get all possible orders
select c.cust_cd. i.item_cd
from customers c, items i
Now find what has actually been ordered by customers
select c.cust_cd, i.item_cd
from customers c, ordheader oh, orddetail od, items i
where c.cust_cd = oh.cust_cd
and od.ord_nbr = oh.ord_nbr
and i.item_cd = od.item_cd
Subtracts orders from potential orders
select c.cust_cd. i.item_cd
from customers c, items i
minus
select c.cust_cd, i.item_cd
from customers c, ordheader oh, orddetail od, items i
where c.cust_cd = oh.cust_cd
and od.ord_nbr = oh.ord_nbr
and i.item_cd = od.item_cd
BTW, I don't recommend you run this particular bit of SQL, as it is
for instructional purposes only. It may cause havoc in your database.
For a smallish data set this query is feasible, but it is mostly just
used to illustrate what needs to be done.
By introducing a table with your list of items, the cartesian join
created by customers and items will become more limited
select c.cust_cd. itc.item_cd
from customers c, items_to_chk itc
minus
select c.cust_cd, i.item_cd
from customers c, ordheader oh, orddetail od, items i
where c.cust_cd = oh.cust_cd
and od.ord_nbr = oh.ord_nbr
and i.item_cd = od.item_cd
That's one simple way to do it.
Had you provided DDL and test data, I would have tested it. :)
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
