CREATE  TABLE #Table1 (ID INT,Country VARCHAR(100),ParentID INT,LevelDepth INT)
CREATE  TABLE #Table2 (ID INT,Seats INT)
INSERT INTO #Table1 VALUES (1,'India',NULL,1),(2,'aus',NULL,1),(3,'ap',1,2),(4,'up',1,2),(5,'perth',2,2),(6,'Khammam',1,3)
INSERT INTO #Table2 VALUES (6,10),(2,5)
DECLARE @ID INT =1
;WITH Country (C1,C2,C3,C4)
AS
(	SELECT Country,NULL,NULL,NULL FROM #Table1 T1 WHERE T1.ParentID IS NULL  AND T1.ID =@ID
	UNION  ALL
	SELECT T1.Country,T2.Country,NULL,NULL FROM #Table1 T1 INNER  JOIN #Table1 T2 ON T1.ID = T2.ParentID WHERE T2.ID NOT IN (Select ID FROM #Table2) AND T1.ID =@ID
	UNION ALL
	SELECT T1.Country,T2.Country,T3.Country,T3.ID FROM #Table1 T1 INNER JOIN #Table1 T2 ON T1.ID = T2.ParentID  LEFT JOIN #Table1 T3 ON T2.ID =T3.LevelDepth 
	WHERE T2.ID NOT IN (Select ID FROM #Table2) AND T1.ID =@ID
)
SELECT DISTINCT * FROM Country

CREATE  TABLE #Table1 (ID INT,Region VARCHAR(100),ParentID INT,LevelDepth INT)
INSERT INTO #Table1 VALUES (1,'India',NULL,1),(2,'Australia',NULL,1),(3,'AndhraPradesh',1,2),(4,'UttarPradesh',1,2),(5,'Perth',2,2),(6,'Krishna',3,1)
DECLARE @ID INT =1
;WITH CTE (ID,ParentID,SortKey,Country,State,District )
AS
(
  SELECT     ID
	    ,ParentID
	    ,CONVERT(nvarchar(1024), Region) AS SortKey
            ,CONVERT(nvarchar(1024), Region) AS Country
            ,CONVERT(nvarchar(1024), NULL) AS State
            ,CONVERT(nvarchar(1024), NULL) AS District
 FROM	     #Table1 WHERE ParentID IS NULL AND ID = @ID
 UNION ALL 
 SELECT	     Children.ID
	    ,Children.ParentID
	    ,CONVERT(NVARCHAR(1024), RTRIM(Parents.SortKey) + ' -->' + Children.Region) AS SortKey 
	    ,CASE WHEN CHARINDEX(' -->',Parents.SortKey) = 0 THEN Parents.SortKey ELSE    LEFT(Parents.SortKey,CHARINDEX(' -->',Parents.SortKey))END AS Country  
			,CASE WHEN  Children.ParentID IN (SELECT ID FROM #Table1 WHERE ParentID IS NULL) THEN Children.Region ELSE RIGHT(Parents.SortKey,LEN(Parents.SortKey)-CHARINDEX(' -->',Parents.SortKey)-3) END AS State  
	    ,CASE WHEN CHARINDEX(' -->',Parents.SortKey) = 0 THEN NULL ELSE CONVERT(NVARCHAR(1024),Children.Region)  END AS District 
 FROM	    #Table1 AS Children 
 INNER JOIN CTE AS Parents 
 ON			Children.ParentID = Parents.ID
)
SELECT Country AS C1,State AS C2,District AS C3,(CASE WHEN District IS NOT NULL THEN ID ELSE NULL END) AS C4  FROM CTE ORDER BY State