Re: Multiple Table NOT IN

From: Mtek <mtek_at_mtekusa.com>
Date: Fri, 2 May 2008 09:51:55 -0700 (PDT)
Message-ID: <f6220a1e-354d-4025-8738-85e8375a2a30@m3g2000hsc.googlegroups.com>


On May 2, 10:57 am, patrick <pgov..._at_u.washington.edu> wrote:
> On May 2, 8: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 -
>
> why not use not exists???
>
> and not exists (select 'x' from engine.customer_order co,
> engine.order_line ol,
> engine.product p
> where ol.order_item_id = isb.order_item_id
> and co.order_id = ol.order_id
> AND ol.product_id = p.product_id
> AND co.customer_id = 951279
> AND p.subproduct_id = 7);
>
> ====>Patrick

Well, the subquery is customer information. The basic theory behind this is that the engine.item_subscription table contains multiple entries of the order_item_id per customer. That is basically the id of the order. So, a customer orders a subscription to something and gets an id for that order. Then the other column in that table, subproduct_id, represents the issue #. So, the bottom half will get the customer who matches the ID (951279) and the newsletter subscription ID (7).

Then, the top part of the query is attempting to get all the subscriptions for the customer's order ID (subquery). This has nothing to do with whether or not the customer has access. It is just getting the list of issue #'s for the customers order.

That part works fine. I'm just looking to return the issues that the customer does not have access to.

So, basically the query should return the issues that are NOT in the engine.item_subscription for that customer. The engine.item_subscriptio is joined to the product.newsletter_issue on the subproduct_id.

Crazy eh? I did not design the system, just administer it.

john Received on Fri May 02 2008 - 11:51:55 CDT

Original text of this message