Re: Multiple Table NOT IN

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

For example:

Customer A purchases subscription XYZ. He gets an order ID of 123. The first issue ID is 1.
So, an entry goes into the ITEM_SUBSCRIPTION table (123, 1); The subscription renews and he now gets access to issue ID 2. So, and entry goes into the ITEM_SUBSCRIPTION table (123, 2); Now, the subscription renews but an error occurs and the entry in the ITEM_SUBSCRIPTION table (123, 3) is missing;

I want to show which issues the customer is missing based on his order.
So, I join the tables getting a list of the subscription & issue details for customer A based on his order ID.

His order ID will exist in the ITEM_SUBSCRIPTION table due to past issues which he purchased. But the combination of the order ID and the issue ID is unique.

The query returns the issues he has access to. I want it to return the missing issues. So, basically the missing issues within that order ID. And of course, the issue ID matches up with an issue detail table.......

Does that help?

John

john Received on Fri May 02 2008 - 12:09:28 CDT