Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

self join table in query

Status
Not open for further replies.

illuzioner

Technical User
Dec 15, 2002
44
US
Hi,

what is the SQL syntax to do a self-join of a table to itself where the left table (tblLeft) pairs with the table on the right (tblRight) when

tblRight.Date = Max(tblRight.Date)
where tblRight.date < tblLeft.Date

tblLeft and tblRight are the same table. I'm guessing this is a Left Join instead of an Inner Join because I'm not matching fields from both tables.

Any help is greatly appreciated!

Lou
 
I don't understand what you are trying to do.

Could you instead, describe the table, provide some sample data and the expected result.
 
Okay, here is what the table (tblCurrent) looks like:

PID IDate CurVal
1 3/15/2002 5
1 3/20/2002 11
1 3/20/2002 7
2 3/25/2002 4
1 3/25/2002 8
2 3/30/2002 9
1 4/15/2002 12
2 4/15/2002 6

grouping by PID, I am trying to calculate the difference in CurVal between a record and the previous record to look like this:

PID IDate CurVal CurValDiff
1 3/15/2002 5 0
1 3/20/2002 11 6
1 3/20/2002 7 -4
1 3/25/2002 8 1
1 4/15/2002 12 4

and likewise for PID=2. so, the row IDate=3/20/2002 is paired up with IDate=3/15/2002, and each row is paired with its previous row, hence I tried the max function where the max is < the IDate of the left table.

There are more fields that I want to do this with and so I would like more than just the CurVal value, but several values from that row.

Let me know if you need more info.

Thanks!

Lou




 
Select t1.PID, t1.Idate as MasterDate, t1.CurVal as MasterVal, Max(t2.Idate) as SlaveDate
From tblCurrent as T1 Inner Join tblCurrent as t2 On
T1.PID = T2.PID
Where t1.IDATE > t2.IDATE
Group By T1.PID, MasterDate, MasterVal

Name this query &quot;QryCurrentDiffVal&quot;

Second query:

Select QryCurrentDiffVal.PID, QryCurrentDiffVal.IDate, QryCurrentDiffVal.MasterVal as Curval, QryCurrentDiffVal.MasterVal - tblCurrent.Curval
From QryCurrentDiffVal Inner Join tblCurrent On
QryCurrentDiffVal.PID = tblCurrent.PID And
QryCurrentDiffVal.Slavedate = tblCurrent.Idate


That's the jist of what you need to do... When I skimmed your question the first time, I missed the fact that you had some Idate's on the same day so this is not an exact solution. You will need to improve it by adding an autonumber field to tblcurrent and thinking a bit.

Sorry I didn't see the whole question to start with.
 
Thank you for this answer. I will play around with it to see if I can make it work for my exact table. This helps a lot.

Best,

Lou
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top