|
|
Posted by TGEAR on 10/02/93 11:50
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 (ted_gear@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, esquel@sommarskog.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
Navigation:
[Reply to this message]
|