Joining tables (or other methods)

[muntz70]

I have the following data:

ItemTable: (as its)
itemid, storeid, descr, OnHandQty

PriceTableas pt)
itemid, price, EffectiveDate

I need to pull all items from the ItemTable and only ONE record from the PriceTable that matches the itemid from the ItemTable. This ONE record has to be the most recent (EffectiveDate). The PriceTable has multiple entries for each itemid (it's a history table).

The end result I'm looking for is as follows:

its.itemid, its.storeid, its.descr, its.onhandqty, pt.price, pt.EffectiveDate

I know this is simple... but I can't seem to get MAX(somedate) to work. I'm either messing up my subqueries or I'm JOINing incorrectly... or grouping incorrectly for that matter. I don't want to post my current code becuase I would like to see clean examples of how to accomplish this.

Thanks.

 

[donutman]

This is one way to do it. I believe it to be a fairly efficient method.

[Blue]SELECT[/Blue] its.itemid[Gray],[/Gray] its.storeid[Gray],[/Gray] 
       its.descr[Gray],[/Gray] its.onhandqty[Gray],[/Gray] 
       pt.price[Gray],[/Gray] pt.EffectiveDate
   [Blue]FROM[/Blue] ItemTable its [Blue]INNER[/Blue] [Gray]JOIN[/Gray] PriceTable PT
   [Blue]ON[/Blue] its.itemid[Gray]=[/Gray]PT.itemid
   [Blue]WHERE[/Blue]  PT.EffectiveDate[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 1 EffectiveDate
          [Blue]FROM[/Blue] PriceTable [Blue]WHERE[/Blue] PT.itemid[Gray]=[/Gray]itemid
          [Blue]ORDER[/Blue] [Blue]BY[/Blue] EffectiveDate [Blue]DESC[/Blue][Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[seadriver]

If you need all rows from the ItemTable, and there's a possibility that some items don't yet have a price history, then you have to do an outer join with the PriceTable, something like:

select its.itemid, its.storeid, its.descr, its.onhandqty, pt.price, pt.EffectiveDate  
from ItemTable its left outer join 
(select ItemID, max(EffectiveDate)
 from PriceTable
 group by ItemID
) pt 
on its.ItemID = pt.ItemID 
order by its.ItemID

Not sure how efficient that will be, but it should get the job done...