How can I get all records for both tables with the latest begin date if exists?

[TGEAR]

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

[ZeldorBlat]

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

[TGEAR]

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

[ZeldorBlat]

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)

[Hugo Kornelis]

On 15 Jun 2006 10:24:08 -0700, TGEAR wrote:

(snip)

Please help

Hi TGEAR,

I just replied to this question in the original thread. Let me know if
anything is stil unclear!

--
Hugo Kornelis, SQL Server MVP