|
|
Posted by TGEAR on 10/02/31 11:50
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 (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]
|