output the latest date for each foreign key

[TGEAR]

I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[ItemSTDPriceHis tory]
GO


CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
[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 ItemSTDPriceHis tory h
ON i.index_id = h.ItemLookUpID
where h.dtbegin = (select max(h.dtbegin) from ItemSTDPriceHis tory
h)
order by i.itemnsn, i.itemdescripti on, 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]

Re: output the latest date for each foreign key

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

TGEAR wrote:[color=blue]
> I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> N'IsUserTable') = 1)
> drop table [dbo].[ItemSTDPriceHis tory]
> GO
>
>
> CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> [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 ItemSTDPriceHis tory h
> ON i.index_id = h.ItemLookUpID
> where h.dtbegin = (select max(h.dtbegin) from ItemSTDPriceHis tory
> h)
> order by i.itemnsn, i.itemdescripti on, 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[/color]

[Erland Sommarskog]

Re: output the latest date for each foreign key

TGEAR (ted_gear@hotma il.com) writes:[color=blue]
> I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> N'IsUserTable') = 1)
> drop table [dbo].[ItemSTDPriceHis tory]
> GO
>
>
> CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> [index_id] [int] NOT NULL ,
> [ItemLookUpID] [int] NOT NULL ,
> [dtbegin] [datetime] NOT NULL ,
> [price] [decimal](18, 0) NOT NULL ,
> ) ON [PRIMARY]
> GO[/color]

This table does not have a primary key? Furthermore what purpose does
index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
[color=blue]
> I try to get the output of the most latest date for each ItemLookUpID[/color]

SELECT a.ItemLookUpID, a.dtbegin, a.price
FROM ItemSTDPriceHis tory a
JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
FROM ItemSTDPriceHis tory
GROUP BY ItemLookUpID) AS b
ON a.ItemLookUpID = b.ItemLookUpID
AND a.dtbegin = b.maxdate
[color=blue]
> these are sample records. (I am sorry I do not know how to write a sql
> statment with records in it)[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[TGEAR]

Re: output the latest date for each foreign key

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

"ItemSTDPriceHi story table" is a child table of "ItemLookUp table"
The itemlookupID in the ItemSTDPriceHis tory 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 ItemSTDPriceHis tory 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:[color=blue]
> TGEAR (ted_gear@hotma il.com) writes:[color=green]
> > I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> > N'IsUserTable') = 1)
> > drop table [dbo].[ItemSTDPriceHis tory]
> > GO
> >
> >
> > CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> > [index_id] [int] NOT NULL ,
> > [ItemLookUpID] [int] NOT NULL ,
> > [dtbegin] [datetime] NOT NULL ,
> > [price] [decimal](18, 0) NOT NULL ,
> > ) ON [PRIMARY]
> > GO[/color]
>
> This table does not have a primary key? Furthermore what purpose does
> index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
>[color=green]
> > I try to get the output of the most latest date for each ItemLookUpID[/color]
>
> SELECT a.ItemLookUpID, a.dtbegin, a.price
> FROM ItemSTDPriceHis tory a
> JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
> FROM ItemSTDPriceHis tory
> GROUP BY ItemLookUpID) AS b
> ON a.ItemLookUpID = b.ItemLookUpID
> AND a.dtbegin = b.maxdate
>[color=green]
> > these are sample records. (I am sorry I do not know how to write a sql
> > statment with records in it)[/color]
>
> 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, esquel@sommarsk og.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[/color]

[TGEAR]

Re: output the latest date for each foreign key

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

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

ItemLookUp table is a parent table of ItemSTDPriceHis tory table.
ItemLookUp.inde x_id is the primary key.
Relationship b/w two table is one-to-many:
ItemLookUp.inde x_id = ItemSTDPriceHis tory.ItemLookUp ID

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:[color=blue]
> Thank you for your help.
> here index_id is a primary key and the combo of ItemLookupID and
> dtbegin is unique.
>
> "ItemSTDPriceHi story table" is a child table of "ItemLookUp table"
> The itemlookupID in the ItemSTDPriceHis tory 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 ItemSTDPriceHis tory 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:[color=green]
> > TGEAR (ted_gear@hotma il.com) writes:[color=darkred]
> > > I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> > > N'IsUserTable') = 1)
> > > drop table [dbo].[ItemSTDPriceHis tory]
> > > GO
> > >
> > >
> > > CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> > > [index_id] [int] NOT NULL ,
> > > [ItemLookUpID] [int] NOT NULL ,
> > > [dtbegin] [datetime] NOT NULL ,
> > > [price] [decimal](18, 0) NOT NULL ,
> > > ) ON [PRIMARY]
> > > GO[/color]
> >
> > This table does not have a primary key? Furthermore what purpose does
> > index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
> >[color=darkred]
> > > I try to get the output of the most latest date for each ItemLookUpID[/color]
> >
> > SELECT a.ItemLookUpID, a.dtbegin, a.price
> > FROM ItemSTDPriceHis tory a
> > JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
> > FROM ItemSTDPriceHis tory
> > GROUP BY ItemLookUpID) AS b
> > ON a.ItemLookUpID = b.ItemLookUpID
> > AND a.dtbegin = b.maxdate
> >[color=darkred]
> > > these are sample records. (I am sorry I do not know how to write a sql
> > > statment with records in it)[/color]
> >
> > 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, esquel@sommarsk og.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[/color][/color]

[TGEAR]

Re: output the latest date for each foreign key

Please help me on this issue. thanks

TGEAR wrote:[color=blue]
> I think I should state more clearly. Sorry for the mess.
>
> SELECT i.*, h.dtbegin, h.price
> FROM ItemLookUp i LEFT OUTER JOIN
> ItemSTDPriceHis tory h ON i.index_id =
> h.ItemLookUpID
> WHERE (h.dtbegin =
> (SELECT MAX(dtbegin)
> FROM ItemSTDPriceHis tory))
> ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC
>
> ItemLookUp table is a parent table of ItemSTDPriceHis tory table.
> ItemLookUp.inde x_id is the primary key.
> Relationship b/w two table is one-to-many:
> ItemLookUp.inde x_id = ItemSTDPriceHis tory.ItemLookUp ID
>
> 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:[color=green]
> > Thank you for your help.
> > here index_id is a primary key and the combo of ItemLookupID and
> > dtbegin is unique.
> >
> > "ItemSTDPriceHi story table" is a child table of "ItemLookUp table"
> > The itemlookupID in the ItemSTDPriceHis tory 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 ItemSTDPriceHis tory 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:[color=darkred]
> > > TGEAR (ted_gear@hotma il.com) writes:
> > > > I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> > > > N'IsUserTable') = 1)
> > > > drop table [dbo].[ItemSTDPriceHis tory]
> > > > GO
> > > >
> > > >
> > > > CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> > > > [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 ItemSTDPriceHis tory a
> > > JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
> > > FROM ItemSTDPriceHis tory
> > > 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, esquel@sommarsk og.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[/color][/color][/color]

[TGEAR]

Re: output the latest date for each foreign key

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

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


ItemLookUp table is a parent table of ItemSTDPriceHis tory table.
ItemLookUp.inde x_id is the primary key.
Relationship b/w two table is one-to-many:
ItemLookUp.inde x_id = ItemSTDPriceHis tory.ItemLookUp ID


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:[color=blue]
> TGEAR (ted_gear@hotma il.com) writes:[color=green]
> > I have an ItemSTDPriceHis tory 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].[ItemSTDPriceHis tory]') and OBJECTPROPERTY( id,
> > N'IsUserTable') = 1)
> > drop table [dbo].[ItemSTDPriceHis tory]
> > GO
> >
> >
> > CREATE TABLE [dbo].[ItemSTDPriceHis tory] (
> > [index_id] [int] NOT NULL ,
> > [ItemLookUpID] [int] NOT NULL ,
> > [dtbegin] [datetime] NOT NULL ,
> > [price] [decimal](18, 0) NOT NULL ,
> > ) ON [PRIMARY]
> > GO[/color]
>
> This table does not have a primary key? Furthermore what purpose does
> index_id serve? I would expect (ItemLookupID, dtbegin) to be unique.
>[color=green]
> > I try to get the output of the most latest date for each ItemLookUpID[/color]
>
> SELECT a.ItemLookUpID, a.dtbegin, a.price
> FROM ItemSTDPriceHis tory a
> JOIN (SELECT ItemLookUpID, maxdate = MAX(dtbegin)
> FROM ItemSTDPriceHis tory
> GROUP BY ItemLookUpID) AS b
> ON a.ItemLookUpID = b.ItemLookUpID
> AND a.dtbegin = b.maxdate
>[color=green]
> > these are sample records. (I am sorry I do not know how to write a sql
> > statment with records in it)[/color]
>
> 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, esquel@sommarsk og.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[/color]

[Hugo Kornelis]

Re: output the latest date for each foreign key

On 14 Jun 2006 16:19:29 -0700, TGEAR wrote:
[color=blue]
>I think I should state more clearly. Sorry for the mess.
>
>SELECT i.*, h.dtbegin, h.price
>FROM ItemLookUp i LEFT OUTER JOIN
> ItemSTDPriceHis tory h ON i.index_id =
>h.ItemLookUp ID
>WHERE (h.dtbegin =
> (SELECT MAX(dtbegin)
> FROM ItemSTDPriceHis tory))
>ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC[/color]

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 ItemSTDPriceHis tory 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 ItemSTDPriceHis tory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(dtbegin)
FROM ItemSTDPriceHis tory 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]

Re: output the latest date for each foreign key

TGEAR (ted_gear@hotma il.com) writes:[color=blue]
> I think I should state more clearly. Sorry for the mess.
>
> SELECT i.*, h.dtbegin, h.price
> FROM ItemLookUp i LEFT OUTER JOIN
> ItemSTDPriceHis tory h ON i.index_id =
> h.ItemLookUpID
> WHERE (h.dtbegin =
> (SELECT MAX(dtbegin)
> FROM ItemSTDPriceHis tory))
> ORDER BY i.itmnumber, i.descript, h.dtbegin, h.price DESC[/color]

This should probably be

SELECT i.*, h.dtbegin, h.price
FROM ItemLookUp i
LEFT JOIN ItemSTDPriceHis tory h
ON i.index_id = h.ItemLookUpID
AND h.dtbegin = (SELECT MAX(h2.dtbegin)
FROM ItemSTDPriceHis tory 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
ItemStdPriceHis tory.

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.)
[color=blue]
> so here is the sample data
>
> h.index_id h.ItemLookupID h.dtbegin h.price
> i.itmnumber i.descript
> --------------------------------------------------------------------------[/color]
----------------------------------------------------[color=blue]
>
> 1 4 4/2/2006 1500 000001[/color]

Since you did not take the occassion to practice INSERT statements, the
query above is not tested.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx