I have a `Locations` table which is self joined making hierarchy of administrative division. Like `State`, `County`, `Town` etc. 
`LocationId` is `PK` of this `table` and it is used as `Foreign key` in `joining table`. Joining table has `ProjectId` and `LocationsId`, Project can be at any level of location so joining table can have county, town etc. I want to find out `root-parent(State)` of location used as FK.
Please use following `sql` as sample.(copy/paste in management studio)

```
DECLARE @Locations TABLE (LocationId INT, LocationName VARCHAR(30), ParentId INT, LocLevel INT)
INSERT INTO @Locations 
Values(1, 'State1', NULL, 1), 
(2, 'State1-County1', 1, 2),
(3, 'State1-County1-Town1', 2, 3),
(4, 'State1-County1-Town1-Muncip-1', 3, 4),
(5, 'State1-County2', 1, 2),
(6, 'State1-County2-Town1', 5, 3),
(7, 'State1-County2-Town1-Muncip-1', 6, 4),
(8, 'State2', NULL, 1), 
(9, 'State2-County1', 8, 2),
(10, 'State2-County1-Town1', 9, 3),
(11, 'State2-County1-Town1-Muncip-1', 10, 4)

DECLARE @ProjectLocations TABLE (ProjectLocationId INT, ProjectId INT, LocationId INT)
INSERT INTO @ProjectLocations
VALUES(1, 1, 2),
(2, 1, 3),
(3, 1, 4),
(4, 1, 11),
(5, 2, 3),
(6, 2, 11),
(7, 3, 10),
(8, 4, 11),
(9, 5, 9)

SELECT * FROM @Locations
SELECT * FROM @ProjectLocations

```

This should be out put

```
DECLARE @FirstOutput TABLE (ProjectLocationId INT, ProjectId INT, LocationId INT, RootParentId INT)
SELECT * FROM @FirstOutput

```