By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,493 Members | 1,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,493 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jun 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a

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

Jun 15 '06 #2

P: n/a
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


Jun 15 '06 #3

P: n/a

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)

Jun 15 '06 #4

P: n/a
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
Jun 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.