Help with CTE recursive access to the second table

Question

My goal is to overwrite the tbl table and, recursing through this table, select the country abbreviation (if it exists) from another tbl2 table and add these results together, which are included in the final output.

An example that I will use comes from this post.

tbl2 has a tbl_id foreign key for tbl and looks like this:

INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
VALUES 
 (100, 'EU', 1) 
,(101, 'AS', 2) 
,(102, 'DE', 3) 
,(103, 'CN', 5)

* Note: not all countries have abbreviations.

The trick is that I want all countries in Asia to at least show the abbreviation Asia, which is “AS,” even if the country does not have an abbreviation (for example, in India). If the country is abbreviated , the result should look like this: China: CN, AS

It partially works for me, using a subquery, but India always returns NULL for an abbreviation. It acts as if there wasn’t a full recursive path back to the abbreviation, then it returns null. Maybe the solution is to use the left outer join in the abbreviation table? I tried for several hours the various options, and the subquery as close as possible.

WITH  abcd 
    AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID, 
                CAST(([Name]) AS VARCHAR(1000)) AS "Path" 
        FROM    @tbl 
        WHERE   ParentId IS NULL 
        UNION ALL 
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name + ':' +                 
                (
                    select t2.abbreviation + ','
                    from @tbl2
                    where t.id = t2.id
                )) AS VARCHAR(1000)) AS "Path"

        FROM    @tbl AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT * FROM abcd

btw, I'm using SQL Server 2005, if that matters

Answer 1

Try this example, which will give you the result (1 sample string)

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

TSQL

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

INSERT INTO @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 @tbl2 table (id int, abbreviation varchar(10), tbl_id int)
INSERT INTO @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
    left join @tbl2 b on a.Id = b.tbl_id
), abcd AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID,
                CAST(([Name]) AS VARCHAR(1000)) [Path],
                cast(abbreviation as varchar(max)) abbreviation
        FROM    abbr
        WHERE   ParentId IS NULL 
        UNION ALL
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name) AS VARCHAR(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