Multiple Table NOT IN

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 1 May 2008 11:05:57 -0700 (PDT)
Message-ID: <a2b93e36-7b9f-45a8-bfcf-3a1fcb3bd402@m45g2000hsb.googlegroups.com>

Hi,

This is the basic query:

  SELECT DISTINCT(ol.order_item_id) order_item_id, ca.customer_id   FROM new_customer.customer c, customer_account ca, engine.item_subscription i,
  engine.subproduct s, engine.subscription sc, engine.customer_order co, engine.order_line ol,
  engine.product p
  WHERE c.customer_id = ca.customer_id

    AND ca.customer_id = co.customer_id
    AND co.order_id = ol.order_id
    AND ol.product_id = p.product_id

    AND p.product_id = 23196
    AND ol.status = 'Active'
    AND p.product_id = sc.product_id
    AND ol.order_item_id = i.order_item_id     AND sc.subproduct_id = i.subproduct_id;

What I am trying to do is find customers who are MISSING access to their reports.

The engine.item_subscription table has 2 columns: subproduct_id & order_item_id. If a record exists with the proper values for the customer, he has access, else he does not.

So I'm trying to find records where the customer has an ACTIVE order but is missing records in the item_subscription table......

We are on Oracle 10g. Hope this is enough info for some help......

Thanks,

John Received on Thu May 01 2008 - 13:05:57 CDT