|
|
Posted by ZeldorBlat on 10/02/27 11:50
TGEAR wrote:
> thank you, I also need to show the price value and I added the column
> in your stmt, but i think i did wrong and I got this message "Column
> 'ih.price' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause."
>
> ZeldorBlat wrote:
> > 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
You can do the following, but it won't work (correctly) if the most
recent beginDate occurs twice for a single item. It would be easier to
simply store the current price of the item in the ItemLookup table.
select i.index_id, i.itemno, i.description, ih2.price, 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
left outer join ItemPriceHistory ih2
on (x.itemlookupID = ih2.itemlookupID
and x.lastBeginDate = ih2.beginDate)
Navigation:
[Reply to this message]
|