How can we get full path of a tree from mysql event data and hierarchical master data? -

i working on product user can read through branched stories. in bi events, capture decision node points user picks 1 node on other. have master data of story content has information of tree. so, our tables this:

user_events:

user_id link_id      1000    1        1000    7             2000    2             2000    6             

so user user_id 1000 has clicked on links 1 , 7, user 2000 has clicked on links 2 , 6. depending on this, both users have traversed different path of nodes. static path details stored in hierarchical data follows:

link info:

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 

at nodes 101, 105 , 106, user can take 2 different paths , 'decision nodes' in story (101 102 or 103; 105 , 106, 107 or 108). capture clicks info in user_events , not intermediate paths paths defined once user clicks on decision node.

now above 2 tables, trying generate full path each user has traversed, follows:

user_id    node_id 1000       101 1000       102 1000       104 1000       105 1000       107 2000       101 2000       103 2000       106 2000       107 

but lost here how achieve this. trying join user events table link info table, first node there. can please shed light on best way achieve this?

try:

with links (select l1.link_id, l2.to_node from_node, l1.from_node to_node link_info l1 inner join link_info l2 on l2.to_node=l1.from_node union select link_id, from_node, to_node  link_info) select e.user_id, l.from_node node_id user_events e inner join links l on l.link_id=e.link_id  union         select e.user_id, l.to_node node_id user_events e inner join links l on l.link_id=e.link_id