On May 2, 11:24=A0am, Mtek <m...@[EMAIL PROTECTED]
> wrote:
> On May 2, 8:13 am, Tom <tzebli...@[EMAIL PROTECTED]
> wrote:
>
>
>
>
>
> > On May 1, 2:05 pm, Mtek <m...@[EMAIL PROTECTED]
> wrote:
>
> > > Hi,
>
> > > This is the basic query:
>
> > > =A0 SELECT DISTINCT(ol.order_item_id) order_item_id, ca.customer_id
> > > =A0 FROM new_customer.customer c, customer_account ca,
> > > engine.item_subscription i,
> > > =A0 engine.subproduct s, engine.subscription sc,
engine.customer_order=
> > > co, engine.order_line ol,
> > > =A0 engine.product p
> > > =A0 WHERE c.customer_id =3D ca.customer_id
> > > =A0 =A0 AND ca.customer_id =3D co.customer_id
> > > =A0 =A0 AND co.order_id =3D ol.order_id
> > > =A0 =A0 AND ol.product_id =3D p.product_id
> > > =A0 =A0 AND p.product_id =3D 23196
> > > =A0 =A0 AND ol.status =3D 'Active'
> > > =A0 =A0 AND p.product_id =3D sc.product_id
> > > =A0 =A0 AND ol.order_item_id =3D i.order_item_id
> > > =A0 =A0 AND sc.subproduct_id =3D i.subproduct_id;
>
> > > What I am trying to do is find customers who are MISSING access to
> > > their re****ts.
>
> > > The engine.item_subscription table has 2 columns: =A0subproduct_id &
> > > order_item_id. =A0If 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. =A0Hope this is enough info for some
help......
>
> > > Thanks,
>
> > > John
>
> > Hi, John,
>
> > In your basic query, there is no join to engine.subproducts. =A0I'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 =3D sc.subproduct_id"? =A0If 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,
> > =A0 =A0 =A0 =A0most_of_the_stuff.customer_id customer_id
> > =A0 =A0 =A0 =A0 =A0 =A0from (SELECT ol.order_item_id order_item_id,
ca.c=
ustomer_id
> > customer_id
> > =A0 =A0 =A0 =A0FROM new_customer.customer c,
> > =A0 =A0 =A0 =A0 =A0 =A0 customer_account ca,
> > =A0 =A0 =A0 =A0 =A0 =A0 engine.subproduct s,
> > =A0 =A0 =A0 =A0 =A0 =A0 engine.subscription sc,
> > =A0 =A0 =A0 =A0 =A0 =A0 engine.customer_order co,
> > =A0 =A0 =A0 =A0 =A0 =A0 engine.order_line ol,
> > =A0 =A0 =A0 =A0 =A0 =A0 engine.product p
> > =A0 =A0 =A0 WHERE c.customer_id =3D ca.customer_id
> > =A0 =A0 =A0 =A0 AND ca.customer_id =3D co.customer_id
> > =A0 =A0 =A0 =A0 AND co.order_id =3D ol.order_id
> > =A0 =A0 =A0 =A0 AND ol.product_id =3D p.product_id
> > =A0 =A0 =A0 =A0 AND p.product_id =3D 23196
> > =A0 =A0 =A0 =A0 AND ol.status =3D 'Active'
> > =A0 =A0 =A0 =A0 AND p.product_id =3D sc.product_id
> > =A0 =A0 =A0 =A0 AND ol.order_item_id =3D i.order_item_id
> > =A0 =A0 =A0 =A0 AND sc.subproduct_id =3D i.subproduct_id)
most_of_the_st=
uff left
> > outer join =A0engine.item_subscription i
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0on
most_of_the_st=
uff.order_item_id =A0=3D i.order_item_id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0and =A0most_of_the_stuff.subproduct_id
=
=3D i.subproduct_id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0where
i.subproduc=
t_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. =A0In order to test the logic
> > or to try variations, you can do something like:
>
> > create table =A0mots (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,
> > =A0 =A0 =A0 =A0most_of_the_stuff.customer_id customer_id
> > =A0 =A0 =A0 =A0 =A0 =A0from (SELECT order_item_id, =A0customer_id,
subpr=
oduct_id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM mots) most_of_the_stuff
left=
outer join subscription
> > i
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0on
most_of_the_st=
uff.order_item_id =A0=3D i.order_item_id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0and =A0most_of_the_stuff.subproduct_id
=
=3D i.subproduct_id
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0where
i.subproduc=
t_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
>
> > | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ---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. =A0I'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 =3D isb.order_item_id
> =A0 AND isb.subproduct_id =3D sp.subproduct_id
> =A0 AND sp.code =3D ni.issue_id
> =A0 AND ni.newsletter_id =3D 7
> =A0 AND isb.order_item_id IN (SELECT ol.order_item_id
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM
engine.custome=
r_order co,
> engine.order_line ol, engine.product p
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE co.order_id
=
=3D ol.order_id
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND
ol.product_=
id =3D p.product_id
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND
co.customer=
_id =3D 951279
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND
p.subproduc=
t_id =3D 7);
>
> And basically this lists the subscription issues a customer HAS access
> to. =A0What I really want are the issues the customer DOES NOT have
> access to. =A0In order to do that, a combination needs to be put
> together where the =A0l.order_item_id !=3D isb.order_item_id AND
> isb.subproduct_id !=3D 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. =A0Above they are compared to be equal, which will omit the ones
> where the customer is missing access. =A0I want the reverse. =A0But 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 =3D isb.order_item_id
AND isb.subproduct_id =3D sp.subproduct_id
AND sp.code =3D ni.issue_id
AND ni.newsletter_id =3D 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 =3D ol.order_id
AND ol.product_id =3D p.product_id
AND co.customer_id =3D 951279
AND p.subproduct_id =3D 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.
=2E . . Tom
|
