Re: Multiple Table NOT IN

On May 1, 2:05 pm, Mtek <m..._at_mtekusa.com> wrote:
> 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

Sorry, but it's not enough information for me.

you say there is a relation between customers and subscriptions. But the only way there I can see from the WHERE clause is thru Orders. And there seems to be a circular relation among order line subscription and item subscription.

I'm lost. perhaps you could post some of the relevant table information (PK/FK)?
ed Received on Fri May 02 2008 - 07:43:28 CDT