| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Best way to do a join with effective dates and see all rows
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 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.
 |
 |