Re: simple query

On Tue, Jul 29, 2008 at 1:56 PM, Lyall Barbour <lyallbarbour_at_sanfranmail.com
> 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

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