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