|
|
Posted by ZeldorBlat on 10/02/55 11:50
TGEAR wrote:
> Itemlookup table
> Field names : index_id (primary key), itemno, description.
> It has a child table, which is ItemPriceHistory table
> The relationship to the child table is one (parent table)-to-many
> (child table). - It is possible to have no child record for some rows
> in the parent table.
>
> ItemPriceHistory table
> Field names: index_id (primary key), itemlookupID (foreign key of the
> Itemlookup table), date begin, price
> It is a child table of the itemlookup table.
>
> How can I get all records for both tables with the latest begin date if
> exists?
> I also need to show the records in the parent table if there is no
> related record in the child table.
>
> Please help
select i.index_id, i.itemno, i.description, x.lastBeginDate
from Itemlookup i
left outer join (select ih.itemlookupID, max(ih.beginDate)
lastBeginDate
from ItemPriceHistory ih
group by ih.itemlookupID) x
on i.index_id = x.itemlookupID
Navigation:
[Reply to this message]
|