How far down the chain do you want to go? 1 step, or until ReplacementKey is NULL?
>Yes and Yes
>
>>I am assuming that if there is a ReplacementKey, you want to pull that record as well? So really, you are doing a recursive query?
>>
>>>I have a table with the following structure:
>>>
>>>
>>>

>>>Key	StartDate			StopDate		ReplacementKey		ReplacementDate
>>>-------------------------------------------------------------------------------------------------------
>>>1	2000-04-12 13:02:00.670		2002-10-17 13:43:56.000		NULL				NULL
>>>6	2000-12-12 14:11:25.000		2001-05-29 17:02:17.000		119					2006-05-21 09:53:06.740
>>>17	2001-09-10 09:34:11.000		2006-05-11 11:02:03.000		NULL				NULL
>>>137	2003-02-03 16:34:37.000		2005-08-05 11:44:58.000		147					2006-06-21 10:53:06.750
>>>141	2003-02-04 09:43:48.000		2005-08-05 11:44:58.000		236					2006-06-21 10:53:06.750
>>>

>>>
>>>
>>>Given a key, how can I pull that record and all if it's historical records as well? Is it possible to do it in one SELECT?
>>>
>>>Thanks