Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Best way to do a join with effective dates and see all rows

Re: Best way to do a join with effective dates and see all rows

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 20 Nov 2006 01:33:34 -0800
Message-ID: <1164015214.406159.153900@e3g2000cwe.googlegroups.com>


Hi, I just re-read your question. I have already told you that my original select was faulty and posted the (hopefully) correct solution. But reading your question once more, it seems you didn't want to point out that I was wrong, but merely wanted to learn how the select worked. Sorry, if I misunderstood. In my select I first select the distinct effective dates by unioning products and subgroups. As this select is part of the from clause, it is a view, i.e. its result gets treated just like a table. I name it Dates and join its records with products and subgroups of appropriate effective dates. Hence Max(Products.EffectiveDate) doesn't refer to the products records that where unioned and got into the view (because the view's records get accessed by the qualifier Dates, not Products). It is the products records instead that I join to the view later. Does that clarify it?

Regards, Thorsten.

PS: The select will most certainly look better with real tables, as they would not contain redundant data like the sample tables do.



Gumbatman schrieb:

> Thorsten,
>
> Thanks for the great answer, I really appreciate it.
>
> Just a quick question, in the first FROM, you have the following:
> select Products.ProductID, Dates.SubGroupID, Dates.EffectiveDate
> , Max(Products.EffectiveDate) as ProductDate
> , Max(SubGroups.EffectiveDate) as SubGroupDate
>
> How is the Products.ProductID referring to the Products table since it is
> based on the FROM that is a Union between the two tables?
>
> I am still working on this, but you have given me a lot to go with.
>
> Thank you!
>
>
> "Thorsten Kettner" <thorsten.kettner_at_web.de> wrote in message
> news:1163688677.769633.234230_at_m73g2000cwd.googlegroups.com...
> > Sorry, I understand it now. I feel so foolish. I was confused by the
> > sample tables' names. I think there is really a products table
> > containing product ids and there related subgroup ids and another table
> > of product names containing the products' names and their effective
> > date? However, the solution to your question is: You want to have one
> > result record for each distinctive date (because either product name or
> > subgroup name changes then). This is your inner select. Then join the
> > two tables, and determine their maximum date for the given distinct
> > date. At last join the tables again adding all the fields you want to
> > see:
> >
> > select Keys.SubGroupId, Products.ProductName, SubGroups.SubGroupName,
> > Keys.EffectiveDate
> > from
> > (
> > select Products.ProductID, Dates.SubGroupID, Dates.EffectiveDate
> > , Max(Products.EffectiveDate) as ProductDate
> > , Max(SubGroups.EffectiveDate) as SubGroupDate
> > from
> > (
> > select distinct SubGroupID, EffectiveDate from Products
> > union
> > select distinct SubGroupID, EffectiveDate from SubGroups
> > ) Dates
> > join Products on (Products.SubGroupID = Dates.SubGroupID and
> > Products.EffectiveDate <= Dates.EffectiveDate)
> > join SubGroups on (SubGroups.SubGroupID = Dates.SubGroupID and
> > SubGroups.EffectiveDate <= Dates.EffectiveDate)
> > group by Products.ProductID, Dates.SubGroupID, Dates.EffectiveDate
> > ) Keys
> > join Products on (Products.ProductID = Keys.ProductID and
> > Products.SubGroupID = Keys.SubGroupID and Products.EffectiveDate =
> > Keys.ProductDate)
> > join SubGroups on (SubGroups.SubGroupID = Keys.SubGroupID and
> > SubGroups.EffectiveDate = Keys.SubGroupDate)
> > order by 4,1,2,3
> >
> > Note 1: Maybe that can be solved more elegantly by using aggregate
> > functions.
> > Note 2: I suppose that your real tables link a distinct subgroup id to
> > each product id, so the part and Products.SubGroupID = Keys.SubGroupID
> > will actually not be needed, as product ids get compared already.
> >
> >
> > -----------------------------------------------
> > Gummy wrote:
> > > > I have two tables:
> > > >
> > > > PRODUCTS
> > > > ProductID ProductName SubGroupID EffectiveDate
> > > > 1 Vanilla A 1/1/2000
> > > > 1 French Vanilla A 1/1/2001
> > > > 1 Yummy Vanilla A 1/1/2003
> > > >
> > > >
> > > > SUBGROUPS
> > > > SubGroupID SubGroupName EffectiveDate
> > > > A Frozen 1/1/2000
> > > > A Ice Cream 2/1/2001
> > > > A Dairy 1/1/2002
> > > >
> > > >
> > > > I want to join them where I see each row from both tables , using the
> > > > effective dates, but without getting duplicates (createing a
> normailzed
> > > > table). Here are the results I would like:
> > > >
> > > > RESULTS
> > > > ProductID ProductName SubGroupName EffectiveDateAll
> > > > 1 Vanilla Frozen 1/1/2000
> > > > 1 French Vanilla Frozen 1/1/2001
> > > > 1 French Vanilla Ice Cream 2/1/2001
> > > > 1 French Vanilla Dairy 1/1/2002
> > > > 1 Yummy Vanilla Dairy 1/1/2003
> >
Received on Mon Nov 20 2006 - 03:33:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US