title: Complex SQL Query: Select like tree traversal
body: <p>Consider the following <code>(1:N)</code> relationship:</p>
  
  <p><code>[entity: user] &lt;------ rid key ------&gt; [entity: rid]</code>.</p>
  
  <p>consider the data in both tables as:</p>
  
  <pre><code>select * from user;
  user-id        rid-key
  a-basa         a
  b-basa         b
  a.a-basa       a.a   
  a.b-basa       a.b
  a.a.a-basa     a.a.a
  a.a.b-basa     a.a.b
  a.b.a-basa     a.b.a
  a.b.b-basa     a.b.b
  a.b.b.a-basa   a.b.b.a
  a.b.b.b-basa   a.b.b.b
  
  
  
  select * from rid;
  
  rid-key    parent-rid    enabled
  a            null        true
  b            null        true
  a.a          a           true 
  a.b          a           false
  a.a.a        a.a         true
  a.b.a        a.b         true
  a.b.b        a.b         true
  a.b.b.a      a.b.b       true
  ......
  n rows
  </code></pre>
  
  <p>I need to design a single query (not stored procedure) which will input a <code>user-id</code>, and the following facts are considered:</p>
  
  <p>If an user is given access to a <code>rid</code>, then it can also access the <code>parent rid</code> of the <code>rid</code> given - the <code>rid</code> itself is enabled <code>(enabled = true).</code></p>
  
  <p>This should continue till we reach the <code>root rid</code>, ie. <code>parent rid</code> property is <code>null</code>.</p>
  
  <p>In above example, the list of accessible rid for the user <code>'a.b.b.a-basa'</code> will be<code>:</code></p>
  
  <pre><code>a.b.b.a
  a.b.b
  a.b
  </code></pre>
  
  <p>and for <code>a.a.a-basa</code>:</p>
  
  <pre><code>a.a.a
  a.a
  a
  </code></pre>
  
  <p>can we get this list using a single query? Any sql vendor is fine.</p>