Re: Multiple Table NOT IN

From: Tom <tzeblisky_at_autooneins.com>
Date: Fri, 2 May 2008 06:13:16 -0700 (PDT)
Message-ID: <c9b254a2-98fd-44bd-a65e-bafb193d9383@d45g2000hsc.googlegroups.com>

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

Hi, John,

In your basic query, there is no join to engine.subproducts. I'm guessing that this was an oversight in simplifying the query for the example. Is it reasonable to assume that the where clause should have "AND s.subproduct_id = sc.subproduct_id"? If it is, you could do something like this:

select distinct order_item_id, customer_id from (
select most_of_the_stuff.order_item_id order_item_id,

       most_of_the_stuff.customer_id customer_id
	   from (SELECT ol.order_item_id order_item_id, ca.customer_id
customer_id
       FROM new_customer.customer c,
            customer_account ca,
            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) most_of_the_stuff left
outer join  engine.item_subscription i
			   on most_of_the_stuff.order_item_id  = i.order_item_id
               and  most_of_the_stuff.subproduct_id = i.subproduct_id
			   where i.subproduct_id is null

);

Now - I didn't create the schemas and tables to test this and depending on your data, there may be more optimal ways to write it but hopefully this will give you some ideas. In order to test the logic or to try variations, you can do something like:

create table mots (order_item_id int, subproduct_id int, customer_id int);
create table subscription (order_item_id int, subproduct_id int);

insert into mots values( 1, 1, 1);
insert into mots values( 1, 2, 1);
insert into mots values( 1, 3, 1);
insert into mots values( 2, 1, 1);
insert into mots values( 2, 2, 1);
insert into mots values( 2, 3, 1);
insert into mots values( 3, 1, 2);
insert into mots values( 3, 2, 2);
insert into mots values( 3, 3, 2);
insert into subscription values(1,1);
insert into subscription values(1,2);
insert into subscription values(1,3);

insert into subscription values(3,1);
insert into subscription values(3,3);
commit;

select distinct order_item_id, customer_id from (
select most_of_the_stuff.order_item_id order_item_id,

       most_of_the_stuff.customer_id customer_id
	   from (SELECT order_item_id,  customer_id, subproduct_id
      		   FROM mots) most_of_the_stuff left outer join subscription
i
			   on most_of_the_stuff.order_item_id  = i.order_item_id
               and  most_of_the_stuff.subproduct_id = i.subproduct_id
			   where i.subproduct_id is null

);

If you were to diagram the query out as you had it, it would look something like:

C -joins to- CA -joins to- CO -joins to- OL -joins to- P -joins toSC  ??-joins to- S  

|                                |
                                                            ---joins

to- I -joins to----

In general (perhaps always) the optimizer doesn't like things like this - it prefers things that look like a tree - at least this is my experience.

Hope this helps.

. . .Tom Received on Fri May 02 2008 - 08:13:16 CDT