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

I know the tables look strange and you wouldn't see them in this format normally. What I did was put it together in a simplified form for the example.

To my understanding having a table with two primary keys (the ProductID and EffectiveDate) is proper way of making a relational database that changes over time.

"Gummy" <gumbatman_at_hotmail.com> wrote in message news:12lnmqsno6iqh2b_at_corp.supernews.com...
> Hello,
>
> 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
>
>
>
> I was using a Left Join on Subgroups.SubGroupID = Products.SubgroupID and
> also connecting the Effective Dates, but that obviously didn't work. I
> thought about referencing the EffectiveDate of the Product table in the
> join to do a Max(EffectiveDate) but I don't know how to do that.
>
> Any help and suggestions are greatly appreciated.
>
> Thank you.
>
>
>
Received on Thu Nov 16 2006 - 05:47:11 CST