|
|
Posted by TGEAR on 06/14/06 22:29
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 (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]
|