U can try this ------------------ DECLARE @Location_ID INT SELECT @Location_ID = 3 ;WITH ret AS( SELECT * FROM Locations WHERE Location_ID = @Location_ID UNION ALL SELECT t.* FROM Locations t INNER JOIN ret r ON t.Parent_ID = r.Location_ID ) SELECT * FROM ret