SELECT current address

Question

Post reply

SELECT current address

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
October 17, 2007 at 8:48 am

#62176

Good Morninig!
I know I have done this before but am struggling with it ...
two tables: Students and Address
CREATE TABLE Student (
StudentID int,
Other stuff ...)
CREATE TABLE Address (
AddressID int,
StudentID int,
HistoryID int,
Other stuff ...)
When a student moves the old address is not deleted, instead the new one is added and the HistoryID is incremented by one so the students current address has the max HistoryID.
How do I get only the current address from:
SELECT a.StudentID, b.AddressID, other stuff ....
FROM Student a JOIN Address b ON a.StudentID = b.StudentID
??
Thanks!
JM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

You will need a derived table to join the 2 tables

select s.*,a.*

from students s

join

(select studentid,max(historyid) as maxhist from address) lastaddress

on s.studentid = lastaddress.studentid

join

address a

on a.studentid = lastaddress.studentid and

a.historyid = lastaddress.maxhist

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 2

Perfect!

Thanks. I have done this in the past but just drew a huge blank.

JM

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

or - change the problem around. Put a trigger on insert to "deactivate" all addresses with same studentID, but lower historyID's. At that point you can just look for addresses where active=1.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 4

If only I had the authority! I really don't like this history approach and it's used all over the place in this db. Oh well .... thanks for the suggestion.