Re: Multiple Table NOT IN

On May 2, 11:24 am, Mtek <m..._at_mtekusa.com> wrote:
> On May 2, 8:13 am, Tom <tzebli..._at_autooneins.com> wrote:
>
>
>
>
>
> > 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 to-
> > SC ??-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
>
> Tom,
>
> I guess the query was not the was not the way to approach this.  I'll
> try to be more clear......
>
> I have this query:
>
> SELECT DISTINCT(ni.issue_id), sp.issue_date, sp.location, sp.name,
> sp.type_id, sp.code, ni.newsletter_id, sp.subproduct_id
> FROM engine.order_line ol, engine.item_subscription isb,
> engine.subproduct sp, product.newsletter_issue ni
> WHERE ol.order_item_id = isb.order_item_id
>   AND isb.subproduct_id = sp.subproduct_id
>   AND sp.code = ni.issue_id
>   AND ni.newsletter_id = 7
>   AND isb.order_item_id IN (SELECT ol.order_item_id
>                            FROM engine.customer_order co,
> engine.order_line ol, engine.product p
>                            WHERE co.order_id = ol.order_id
>                              AND ol.product_id = p.product_id
>                              AND co.customer_id = 951279
>                              AND p.subproduct_id = 7);
>
> And basically this lists the subscription issues a customer HAS access
> to.  What I really want are the issues the customer DOES NOT have
> access to.  In order to do that, a combination needs to be put
> together where the  l.order_item_id != isb.order_item_id AND
> isb.subproduct_id != sp.subproduct_id.
>
> So, they values to be compared to the columns in the
> engine.item_subscription isb table are coming from 2 different
> tables.  Above they are compared to be equal, which will omit the ones
> where the customer is missing access.  I want the reverse.  But I do
> not know how to do a NOT IN for 2 separate tables which still keeping
> the rest of the criteria.......
>
> Is this more clear?
>
> john- Hide quoted text -
>
> - Show quoted text -

Hi John,

It seems to me that the simplist solution is to just negate the sub query:

 SELECT DISTINCT(ni.issue_id), sp.issue_date, sp.location, sp.name,  sp.type_id, sp.code, ni.newsletter_id, sp.subproduct_id  FROM engine.order_line ol, engine.item_subscription isb,  engine.subproduct sp, product.newsletter_issue ni  WHERE ol.order_item_id = isb.order_item_id    AND isb.subproduct_id = sp.subproduct_id    AND sp.code = ni.issue_id
   AND ni.newsletter_id = 7
   AND isb.order_item_id NOT IN (SELECT ol.order_item_id

                            FROM engine.customer_order co,
                                         engine.order_line ol,
engine.product p
                            WHERE co.order_id = ol.order_id
                              AND ol.product_id = p.product_id
                              AND co.customer_id = 951279
                              AND p.subproduct_id = 7);

However, doing this probably will not be good for performance - however, perhaps it will not matter depending on your data.

After lunch, I'll take another look at it.

. . . Tom Received on Fri May 02 2008 - 10:47:55 CDT