title: SQL query to get full path from node
body: <p>I have a tree structure table, and I need to find the full path of an element, but in the same time, the elements are restricted by permissions</p>
  
  <p>I have one table <code>tblMapping</code></p>
  
  <pre><code>map_id  name     parent_id
  1       Root     0
  2       Child 1  1
  3       Child 2  1
  4       Child 3  3
  5       Child 4  4
  6       Child 5  5
  </code></pre>
  
  <p>And a <code>tblPerms</code> table:</p>
  
  <pre><code>perm_id map_id
  1       5
  2       6
  </code></pre>
  
  <p>I am using the following CTE query:</p>
  
  <pre><code>;WITH Hierarchy (map_id, name, parent_id, Path)
  AS
  (
      SELECT 
          t.map_id, t.name, t.parent_id, 
          CAST(t.name AS varchar(max)) 
      FROM 
          tblMapping t
      LEFT OUTER JOIN 
          tblMapping t1 ON t1.map_id = t.parent_id 
      LEFT OUTER JOIN 
          tblPerms t2 ON t2.map_id = t1.map_id
      WHERE 
          t1.map_id IS NULL
  
      UNION ALL
  
      SELECT 
          t.map_id, t.name,t.parent_id, 
          CAST(h.Path + '.' + t.name AS varchar(max)) 
      FROM 
          Hierarchy h
      INNER JOIN 
          tblMapping t ON t.parent_id = h.map_id
  )
  SELECT Path
  FROM Hierarchy
  WHERE Path is not null
  OPTION (MAXRECURSION 0)
  </code></pre>
  
  <p>That CTE query will give me the path similiar to <code>Child 4.Child 5</code></p>
  
  <p>What I would like to have is to be able to have the full path of elements, even if permission is not set for:  <code>Root.Child 1.Child 3.Child 4.Child 5</code></p>
  
  <p>Any idea on how to solve this request?</p>
  
  <p>Thank you!</p>