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

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 Wed Nov 15 2006 - 21:30:37 CST