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

I dont understand your question. You have a table called Products where different products all have the same product id??? Then you have a Subgroups table where different subgroups have the same subgroup id??? Then you want to join them somehow, linked by subgroup id and effective date, but somehow you just want some of the records joined and others not. For instance: Product '1 Vanilla A' is effective as of 1/1/2000. Subgroup 'A Ice Cream' is effective as of 2/1/2001. So shouldn't you get '1 Vanilla Ice Cream 2/1/2001' in your result set? Or is there an effective-out date that we don't see? Or any other reason why this pair doesn't get linked?

Gummy wrote:

> 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 - 02:08:34 CST