title: Help with recursive CTE query joining to a second table
body: <p>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 together which are included in the final output.</p>
  
  <p>The example I'll use will come <a href="https://stackoverflow.com/questions/3187850/how-does-a-recursive-cte-run-line-by-line">from this post</a></p>
  
  <p>tbl2 has a Foreign Key 'tbl_id' to tbl and looks like this</p>
  
  <pre><code>INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
  VALUES 
   (100, 'EU', 1) 
  ,(101, 'AS', 2) 
  ,(102, 'DE', 3) 
  ,(103, 'CN', 5)
  </code></pre>
  
  <p>*Note: not all the countries have abbreviations.</p>
  
  <p>The trick is, I want all the countries in Asia to at least show the abbreviation of Asia which is 'AS' even if a country doesn't have an abbreviation (like India for example). If the country does have an abbreviation <strong>the result needs to look like this: China:CN,AS</strong></p>
  
  <p>I've got it partly working using a subquery, but India always returns NULL for the abbreviation.  It's acting like if there isn't a full recursive path back to the abbreviation, then it returns null.  Maybe the solution is to use a left outer join on the abbreviation table? I've tried for hours many different variations and the subquery is as close as I can get.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>btw, I'm using sql server 2005 if that matters</p>