SELECT a.TerritoryId, a.Name, l.levelId, l.name levelName, l.rank FROM tbl_TER_Territories a WITH (NOLOCK) INNER JOIN tbl_TER_TerritoryLevels l WITH (NOLOCK) ON l.levelid = a.levelid AND l.ownerId = a.ownerId AND l.fsl_tablecode = a.fsl_tablecode WHERE a.OwnerID = AND a.FSL_TableCode = AND TerritoryID in (SELECT ParentTerritoryID FROM tbl_TER_Territories WITH (NOLOCK) WHERE OwnerID = a.OwnerID AND FSL_TableCode = a.FSL_TableCode AND ParentTerritoryID > 0 ) ORDER BY l.rank, a.name FOR json path, include_null_values