title: Value in one field as lookup from same table
body: <p>I'm certain this is very easy, but I am very poor at database stuff... </p>
  
  <p>I have the following table in access 2003:</p>
  
  <pre><code>title        |     id
  /root        |      1
  /root/x      |      2
  /root/x/y    |      3
  /root/x/y/z  |      4
  /root/x/a    |      5
  /root/x/a/b  |      6
  </code></pre>
  
  <p>i.e. a bunch of nodes and id numbers - you can see that /root/x is the parent of /root/x/y. I'd like to create another table which has a list of all the nodes, along with the id's of their parents. i.e:</p>
  
  <pre><code>id  | parent id
  1   |   -
  2   |   1
  3   |   2
  4   |   3
  5   |   2
  6   |   5
  </code></pre>
  
  <p>The follwing will give me the id and the value of the parent: </p>
  
  <pre><code>select id, left(c.title, instrrev(c.title, "/")-1)  as parentValue from nodeIDs
  </code></pre>
  
  <p>yields</p>
  
  <pre><code>id |  parentNode
  1  |
  2  |  /root 
  3  |  /root/x 
  4  |  /root/x/y
  5  |  /root/x
  6  |  /root/x/a
  </code></pre>
  
  <p>What is the extra step needed to return the id's of those parent nodes, rather than their values, i.e, return '1' instead of '/root' in that last table?</p>
  
  <p>Many thanks</p>