Re: Multiple Table NOT IN

On May 2, 10:47 am, Tom <tzebli..._at_autooneins.com> wrote:
> 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

Thanks Tom. I did try negating it. The subquery has to do with Customer information and not with the subscription issues. I want to look at outer joins. Maybe I can find something there?

John Received on Fri May 02 2008 - 10:51:21 CDT