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

"Thorsten Kettner" <thorsten.kettner_at_web.de> wrote in news:1163664514.729493.143100_at_b28g2000cwb.googlegroups.com...
>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.
>

LOOKS like it's still the same product but with a different name (on the effective date) and a missing master table with one entry for product 1 (same for groups)

Shakespeare Received on Thu Nov 16 2006 - 03:49:31 CST