Re: Multiple Table NOT IN
Date: Fri, 2 May 2008 10:50:34 -0700 (PDT)
Message-ID: <19dc73b3-4324-4f93-ae5d-a3dcebe7569d@b64g2000hsa.googlegroups.com>
On May 2, 12:16 pm, Mtek <m..._at_mtekusa.com> wrote:
> 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 for 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
Well, this actually works. For whatever reason. But I wish I could cut down on the criteria listing......
SELECT DISTINCT(ni.issue_id), sp.issue_date, sp.location, sp.name,
sp.type_id, sp.code, ni.newsletter_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 sp.code = ni.issue_id AND ni.newsletter_id = 7 AND ol.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 ol.status = 'Active' AND p.subproduct_id = 7)MINUS
SELECT DISTINCT(ni.issue_id), sp.issue_date, sp.location, sp.name, sp.type_id, sp.code, ni.newsletter_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 ol.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 ol.status = 'Active' AND p.subproduct_id = 7);Received on Fri May 02 2008 - 12:50:34 CDT