output the latest date for each foreign key

[TGEAR]

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO
I try to get the output of the most latest date for each ItemLookUpID
and
these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)
index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 4 4/2/2006 1500
2 4 4/8/2006 2000
3 3 4/1/2006 50
4 3 5/7/2006 80
5 2 8/4/2006 67
6 2 9/4/2006 55
7 2 9/6/2006 500

I wrote the sql stmt as below, but it only fetched as below (the most
latest date among all records which is wrong).
index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 4 5/7/2006 80
select i.*, h.dtbegin, h.price
from ItemLookUp i RIGHT OUTER JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
where h.dtbegin = (select max(h.dtbegin) from ItemSTDPriceHistory
h)
order by i.itemnsn, i.itemdescription, i.itemunit, h.dtbegin, h.price
asc
so the correct output will be as below (the latest date with each
itemlookupID) and please help with my sql stmt to output the records
as below

index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 2 9/6/2006 500
2 3 5/7/2006 80
3 4 4/8/2006 2000

[TGEAR]

the reply status shows 1 new of 2
i think someone replied for my posting, but cannot see it.
what happened?

TGEAR wrote:

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO
I try to get the output of the most latest date for each ItemLookUpID
and
these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)
index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 4 4/2/2006 1500
2 4 4/8/2006 2000
3 3 4/1/2006 50
4 3 5/7/2006 80
5 2 8/4/2006 67
6 2 9/4/2006 55
7 2 9/6/2006 500

I wrote the sql stmt as below, but it only fetched as below (the most
latest date among all records which is wrong).
index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 4 5/7/2006 80
select i.*, h.dtbegin, h.price
from ItemLookUp i RIGHT OUTER JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
where h.dtbegin = (select max(h.dtbegin) from ItemSTDPriceHistory
h)
order by i.itemnsn, i.itemdescription, i.itemunit, h.dtbegin, h.price
asc
so the correct output will be as below (the latest date with each
itemlookupID) and please help with my sql stmt to output the records
as below

index_id ItemLookupID dtbegin price
---------------------------------------------------------------------------*----------------

1 2 9/6/2006 500
2 3 5/7/2006 80
3 4 4/8/2006 2000

[Erland Sommarskog]

TGEAR (te******@hotmail.com) writes:

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO
This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
I try to get the output of the most latest date for each ItemLookUpID
SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate
these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)

You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:

INSERT tbl(col1, col2, col3, ...)

(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)

The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:

VALUES (val1, val2, val3, ...)

The values can be expressions, but cannot include queries.

That's the basics. Now you can practice. :-) When in doubt consult Books
Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[TGEAR]

Thank you for your help.
here index_id is a primary key and the combo of ItemLookupID and
dtbegin is unique.

"ItemSTDPriceHistory table" is a child table of "ItemLookUp table"
The itemlookupID in the ItemSTDPriceHistory table is the foreign key of
the ItemLookUp table (Parent table).

I also need to list all values from the parent table as well even
though there is no record existed in the ItemSTDPriceHistory table
(child table)
..
When I run your kind query, if there is no value in the child table, no
output is appeared.
I tried to show the output from the parent table even though there is
no data in the child table and so I used RIGHT OUTER JOIN in my sql
stmt.

I changed your INNER JOIN to RIGHT OUTER JOIN, but nothing was
appeared.
Can you help me on this issue again? thanks in advance.

btw, your step by step instruction of INSERT statement is very helpful.

Erland Sommarskog wrote:

TGEAR (te******@hotmail.com) writes:

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO

This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.

I try to get the output of the most latest date for each ItemLookUpID

SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate

these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)

You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:

INSERT tbl(col1, col2, col3, ...)

(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)

The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:

VALUES (val1, val2, val3, ...)

The values can be expressions, but cannot include queries.

That's the basics. Now you can practice. :-) When in doubt consult Books
Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[TGEAR]

I think I should state more clearly. Sorry for the mess.

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i LEFT OUTER JOIN
ItemSTDPriceHistory h ON i.index_id =
h.ItemLookUpID
WHERE (h.dtbegin =
(SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory))
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC

ItemLookUp table is a parent table of ItemSTDPriceHistory table.
ItemLookUp.index_id is the primary key.
Relationship b/w two table is one-to-many:
ItemLookUp.index_id = ItemSTDPriceHistory.ItemLookUpID

I used OUTER JOIN there since I need to get the parent records even
though if there is no data in the child table.
Also, If i run that sql stmt, i only get one data, but I need to get
the latest date for each foreign key.

so here is the sample data

h.index_id h.ItemLookupID h.dtbegin h.price
i.itmnumber i.descript
---------------------------------------------------------------------------**---------------------------------------------------

1 4 4/2/2006 1500 000001
paper
2 4 4/8/2006 2000 000002
eraser
3 3 4/1/2006 50 000001
pencil
4 3 5/7/2006 80 000002
ballpen
5 2 8/4/2006 67 000001
keyboard
6 2 9/4/2006 55 000002
mornitor
7 2 9/6/2006 500 000003
calendar

And Expected output:

h.index_id h.ItemLookupID h.dtbegin h.price
i.itmnumber i.descript
---------------------------------------------------------------------------**------------------------------------------------
1 2 9/6/2006 500
000003 calendar
2 3 5/7/2006 80
000002 ballpen
3 4 4/8/2006 2000
000002 eraser

TGEAR wrote:

Thank you for your help.
here index_id is a primary key and the combo of ItemLookupID and
dtbegin is unique.

"ItemSTDPriceHistory table" is a child table of "ItemLookUp table"
The itemlookupID in the ItemSTDPriceHistory table is the foreign key of
the ItemLookUp table (Parent table).

I also need to list all values from the parent table as well even
though there is no record existed in the ItemSTDPriceHistory table
(child table)
.
When I run your kind query, if there is no value in the child table, no
output is appeared.
I tried to show the output from the parent table even though there is
no data in the child table and so I used RIGHT OUTER JOIN in my sql
stmt.

I changed your INNER JOIN to RIGHT OUTER JOIN, but nothing was
appeared.
Can you help me on this issue again? thanks in advance.

btw, your step by step instruction of INSERT statement is very helpful.

Erland Sommarskog wrote:

TGEAR (te******@hotmail.com) writes:

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO

This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.

I try to get the output of the most latest date for each ItemLookUpID

SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate

these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)

You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:

INSERT tbl(col1, col2, col3, ...)

(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)

The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:

VALUES (val1, val2, val3, ...)

The values can be expressions, but cannot include queries.

That's the basics. Now you can practice. :-) When in doubt consult Books
Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[TGEAR]

Please help me on this issue. thanks

TGEAR wrote:

I think I should state more clearly. Sorry for the mess.

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i LEFT OUTER JOIN
ItemSTDPriceHistory h ON i.index_id =
h.ItemLookUpID
WHERE (h.dtbegin =
(SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory))
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC

ItemLookUp table is a parent table of ItemSTDPriceHistory table.
ItemLookUp.index_id is the primary key.
Relationship b/w two table is one-to-many:
ItemLookUp.index_id = ItemSTDPriceHistory.ItemLookUpID

I used OUTER JOIN there since I need to get the parent records even
though if there is no data in the child table.
Also, If i run that sql stmt, i only get one data, but I need to get
the latest date for each foreign key.

so here is the sample data

h.index_id h.ItemLookupID h.dtbegin h.price
i.itmnumber i.descript
---------------------------------------------------------------------------**---------------------------------------------------

1 4 4/2/2006 1500 000001
paper
2 4 4/8/2006 2000 000002
eraser
3 3 4/1/2006 50 000001
pencil
4 3 5/7/2006 80 000002
ballpen
5 2 8/4/2006 67 000001
keyboard
6 2 9/4/2006 55 000002
mornitor
7 2 9/6/2006 500 000003
calendar

And Expected output:

h.index_id h.ItemLookupID h.dtbegin h.price
i.itmnumber i.descript
---------------------------------------------------------------------------**------------------------------------------------
1 2 9/6/2006 500
000003 calendar
2 3 5/7/2006 80
000002 ballpen
3 4 4/8/2006 2000
000002 eraser

TGEAR wrote:

Thank you for your help.
here index_id is a primary key and the combo of ItemLookupID and
dtbegin is unique.

"ItemSTDPriceHistory table" is a child table of "ItemLookUp table"
The itemlookupID in the ItemSTDPriceHistory table is the foreign key of
the ItemLookUp table (Parent table).

I also need to list all values from the parent table as well even
though there is no record existed in the ItemSTDPriceHistory table
(child table)
.
When I run your kind query, if there is no value in the child table, no
output is appeared.
I tried to show the output from the parent table even though there is
no data in the child table and so I used RIGHT OUTER JOIN in my sql
stmt.

I changed your INNER JOIN to RIGHT OUTER JOIN, but nothing was
appeared.
Can you help me on this issue again? thanks in advance.

btw, your step by step instruction of INSERT statement is very helpful.

Erland Sommarskog wrote:

TGEAR (te******@hotmail.com) writes:
> I have an ItemSTDPriceHistory table as below and this is a child table
> of itemlookup table with one to many relationship.
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[ItemSTDPriceHistory]
> GO
>
>
> CREATE TABLE [dbo].[ItemSTDPriceHistory] (
> [index_id] [int] NOT NULL ,
> [ItemLookUpID] [int] NOT NULL ,
> [dtbegin] [datetime] NOT NULL ,
> [price] [decimal](18, 0) NOT NULL ,
> ) ON [PRIMARY]
> GO

This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.

> I try to get the output of the most latest date for each ItemLookUpID

SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate

> these are sample records. (I am sorry I do not know how to write a sql
> statment with records in it)

You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:

INSERT tbl(col1, col2, col3, ...)

(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)

The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:

VALUES (val1, val2, val3, ...)

The values can be expressions, but cannot include queries.

That's the basics. Now you can practice. :-) When in doubt consult Books
Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ds/books..mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[TGEAR]

think I should state more clearly. Sorry for the mess.

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i LEFT OUTER JOIN
ItemSTDPriceHistory h ON i.index_id =
h.ItemLookUpID
WHERE (h.dtbegin =
(SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory))
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
ItemLookUp table is a parent table of ItemSTDPriceHistory table.
ItemLookUp.index_id is the primary key.
Relationship b/w two table is one-to-many:
ItemLookUp.index_id = ItemSTDPriceHistory.ItemLookUpID
I used OUTER JOIN there since I need to get the parent records even
though if there is no data in the child table.
Also, If i run that sql stmt, i only get one data, but I need to get
the latest date for each foreign key.
Erland Sommarskog wrote:

TGEAR (te******@hotmail.com) writes:

I have an ItemSTDPriceHistory table as below and this is a child table
of itemlookup table with one to many relationship.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ItemSTDPriceHistory]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHistory]
GO
CREATE TABLE [dbo].[ItemSTDPriceHistory] (
[index_id] [int] NOT NULL ,
[ItemLookUpID] [int] NOT NULL ,
[dtbegin] [datetime] NOT NULL ,
[price] [decimal](18, 0) NOT NULL ,
) ON [PRIMARY]
GO

This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.

I try to get the output of the most latest date for each ItemLookUpID

SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHistory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHistory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate

these are sample records. (I am sorry I do not know how to write a sql
statment with records in it)

You seem to know how to write SELECT statement. Then you should know
how to write INSERT statements as well. It's simple anyway. First you
say:

INSERT tbl(col1, col2, col3, ...)

(columns that are nullable or have default values can be left out. IDENTITY
and timestamp columns must be left out.)

The INSERT clause is followed either by a SELECT statement, or a VALUES
clause if you only want to insert a row from constants or variables:

VALUES (val1, val2, val3, ...)

The values can be expressions, but cannot include queries.

That's the basics. Now you can practice. :-) When in doubt consult Books
Online.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On 14 Jun 2006 16:19:29 -0700, TGEAR wrote:

I think I should state more clearly. Sorry for the mess.

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i LEFT OUTER JOIN
ItemSTDPriceHistory h ON i.index_id =
h.ItemLookUpID
WHERE (h.dtbegin =
(SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory))
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC

Hi TGEAR,

The WHERE clause effectively turns the outer join back into an inner
join. Remember that rows retained by the outer join get a bunch of NULLs
for the columns in the other table (the one alised as h in your query).
So any WHERE cllause that involves those column will automatically
remove them again, since a comparison against NULL can never evaluate to
true. You can fix this problem by moving the dtbegin test from the WHERE
clause to the ON clause.

Also, you need to correlate the subquery against the main query. As yoou
have written the query, only ItemSTDPriceHistory with a date of 9/6/2006
(BTW, is that June 9th or September 6th?) will be selected, since that
is the MAX(dtbegin) in that table.

Try this query instead:

SELECT i.*, -- NEVER USE SELECT * IN PRODUCTION CODE !!!!!!
h.dtbegin, h.price
FROM ItemLookUp i
LEFT JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory AS h2
WHERE h2.ItemLookUpID = i.index_id)
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

TGEAR (te******@hotmail.com) writes:

I think I should state more clearly. Sorry for the mess.

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i LEFT OUTER JOIN
ItemSTDPriceHistory h ON i.index_id =
h.ItemLookUpID
WHERE (h.dtbegin =
(SELECT MAX(dtbegin)
FROM ItemSTDPriceHistory))
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
This should probably be

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i
LEFT JOIN ItemSTDPriceHistory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(h2.dtbegin)
FROM ItemSTDPriceHistory h2
WHERE h2.ItemLookUpID = h.ItemLookupID
ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC

1) You must correlate the subquery with the outer instance of
ItemStdPriceHistory.

2) If you have a LEFT JOIN, and then have a WHERE condition which includes
the right-hand side of the table, you have effectively made the outer
join an inner join, as you filter all rows where the columns evaluates
to NULL on the right. (FROM - JOIN are evaluated before WHERE.)
so here is the sample data

h.index_id h.ItemLookupID h.dtbegin h.price
i.itmnumber i.descript
-------------------------------------------------------------------------- -**---------------------------------------------------
1 4 4/2/2006 1500 000001

Since you did not take the occassion to practice INSERT statements, the
query above is not tested.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx