title: T-SQL Recursion; Multiple Recursions?
body: <p>I'm trying to conceptualize a solution to a problem I have with recursion and I can't quite wrap my mind around it. I have three tables. We'll call them <code>DocGroup</code>, <code>GroupGroup</code>, and <code>GroupUser</code>. In the <code>DocGroup</code> table there is a hirerachial structure of one record, which specifies another record as it's parent and so on until a record is its own parent.</p>
  
  <pre><code>Doc Group
  1   1
  2   2
  3   2
  4   3
  5   2
  6   4
  </code></pre>
  
  <p><code>GroupGroup</code> contains Group nesting:</p>
  
  <pre><code>Group    MemberGroup
  4        2
  4        1
  </code></pre>
  
  <p><code>GroupUser</code> maps a group to a number of users:</p>
  
  <pre><code>Group    User   Key
  1        1      ABC
  1        3      BCD
  1        4      CDE
  2        1      DEF
  2        2      EFG
  2        3      FGH
  3        3      GHI
  4        2      HIJ
  4        3      IJK
  4        4      JKL
  </code></pre>
  
  <p>So I would like to specify a user and a document and get a list of all the keys for that combination. For example if I choose user 2 and document 2 I need to return "EFG" but if I choose document 6 and user 3 I need to return "IJK", "GHI", "FGH", and "BCD" because group 2 and 1 are in group 4 and group 4 = document 4, which has a group of 3. In all cases I only need to get the record for the specified user.</p>
  
  <p>I would imagine that I need multiple recursions to get this done, but I can't seem to mentally map out how that might be done in SQL and I don't want to degrade to loops and cursors to do this.</p>