title: How can we get full path of a tree from mysql event data and hierarchical master data?
body: <p>I am working on a product where user can read through branched stories. In the BI events, we capture only the decision node points where user picks one node over the other. We also have a master data of the story content which has information of the tree. So, our tables look like this:</p>
  
  <p>user_events:</p>
  
  <pre><code>user_id link_id     
  1000    1       
  1000    7            
  2000    2            
  2000    6            
  </code></pre>
  
  <p>So the user with user_id 1000 has clicked on the links 1 and 7, where user 2000 has clicked on links 2 and 6. Depending on this, both users have traversed a different path of nodes. The static path details are stored in a hierarchical data as follows:</p>
  
  <p>Link Info:</p>
  
  <pre><code>link_id         from_node    to_node    
  1                101          102
  2                101          103
  3                102          104
  4                104          105
  5                103          106
  6                106          107
  7                105          107
  8                106          108
  9                105          108
  </code></pre>
  
  <p>At nodes 101, 105 and 106, a user can take two different paths and they are 'decision nodes' in the story (101 to 102 or 103; 105 and 106, to 107 or 108). So we capture only those clicks info in the user_events and not the intermediate paths as that paths are defined once the user clicks on a decision node.</p>
  
  <p>Now from the above two tables, I am trying to generate the full path each user has traversed, as follows:</p>
  
  <pre><code>user_id    node_id
  1000       101
  1000       102
  1000       104
  1000       105
  1000       107
  2000       101
  2000       103
  2000       106
  2000       107
  </code></pre>
  
  <p>But I am lost here as to how to achieve this. I am trying to join the user events table with link info table, but I could get only the first node there. Can someone please shed some light on the best way to achieve this?</p>