|
|
Posted by TGEAR on 10/02/18 11:50
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
Navigation:
[Reply to this message]
|