Re: Multiple Table NOT IN

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 Received on Fri May 02 2008 - 10:24:30 CDT