Help With Recursive Cte Query Joining To A Second Table

My objective is to recurse through table tbl and while recursing through that table select a country abbreviation (if it exists) from another table tbl2 and append those results to

Solution 1:

Try this example, which will give you the output (1 sample row)

id  Name    ParentID    Path    abbreviation    (No column name)
5   China   2   Asia/China  CN,AS   Asia/China:CN,AS

The TSQL being

DECLARE@tblTABLE ( 
     Id INT 
    ,[Name] VARCHAR(20) 
    ,ParentId INT 
    ) 

INSERTINTO@tbl( Id, Name, ParentId ) 
VALUES 
 (1, 'Europe', NULL) 
,(2, 'Asia',   NULL) 
,(3, 'Germany', 1) 
,(4, 'UK',      1) 
,(5, 'China',   2) 
,(6, 'India',   2) 
,(7, 'Scotland', 4) 
,(8, 'Edinburgh', 7) 
,(9, 'Leith', 8) 

; 
DECLARE@tbl2table (id int, abbreviation varchar(10), tbl_id int)
INSERTINTO@tbl2( Id, Abbreviation, tbl_id ) 
VALUES 
 (100, 'EU', 1) 
,(101, 'AS', 2) 
,(102, 'DE', 3) 
,(103, 'CN', 5)

;WITH abbr AS (
    SELECT a.*, isnull(b.abbreviation,'') abbreviation
    FROM@tbl a
    leftjoin@tbl2 b on a.Id = b.tbl_id
), abcd AS ( 
          -- anchor SELECT  id, [Name], ParentID,
                CAST(([Name]) ASVARCHAR(1000)) [Path],
                cast(abbreviation asvarchar(max)) abbreviation
        FROM    abbr
        WHERE   ParentId ISNULLUNIONALL--recursive member SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path +'/'+ t.Name) ASVARCHAR(1000)) [Path],
                isnull(nullif(t.abbreviation,'')+',', '') + a.abbreviation
        FROM    abbr AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT*, [Path] +':'+ abbreviation
FROM abcd