Ok, first off, hopefully this will be at least understandable - medication has my head swimming right now. Second, if this is really simple, it's the drugs making me miss it. 
Basically, I have a table (SQL 2000, not 2005) with a parent/child structure for three levels. Something like this:
ID Name ParentID Type
1 USA null Country
2 South 1 Region
3 East 1 Region
4 Texas 2 City
5 Boston 3 City
What I need to do is resolve the Country from the city level as simply as possible. Ultimately, I need to use it in a join from another table storing employee data. i.e. Select the employees who have a city_id in USA.
I'm running around in circles (or circular references) on this at the moment, so any help would be most appreciated.
Basically, I have a table (SQL 2000, not 2005) with a parent/child structure for three levels. Something like this:
ID Name ParentID Type
1 USA null Country
2 South 1 Region
3 East 1 Region
4 Texas 2 City
5 Boston 3 City
What I need to do is resolve the Country from the city level as simply as possible. Ultimately, I need to use it in a join from another table storing employee data. i.e. Select the employees who have a city_id in USA.
I'm running around in circles (or circular references) on this at the moment, so any help would be most appreciated.