This is my first post on this forum. Hopefully someone can help me. I am trying to get a count in a dbtable of a tree-like structure. The way it works is: I have three fields: member id, left node id, right node id.
So, table would look like
member_id | leftnode_id | rightnode_id
101 | 102 | 103
102 | 104 | 105
103 | 106 | 0
104 | 107 | 108
105 | 109 | 0
106 | 0 | 0
107 | 110 | 0
108 | 0 | 0
109 | 0 | 0
I'm trying to parse the db such that for every member_id, it gives me a left node count, i.e. how many nodes are on the left side. for example member 101's left node count will include total node count of member 102 that is anything below, and right node count will have node count of member 103.
I tried using sql parsing and creating the tree on the fly, but the script fails as the numbers of members increases.
Is it possible to find the count in reverse order, where the db first goes thru the last member_id, and generates the node count in reverse.
In this case, it would go to member 109 and in leftnode count put 0 and right node count put 0, same with 108 and 106.
For member 105, the left node count will be 1 which is just node 109 and right node count will be 0.
For member 104, the left node count will be 1 + node count of 110 (1) = 2 and right node count will be 1.
For member 103, the left node count will be 1.
For member 102, left node count will be 1 + 2 (node count of 104) = 3 and right node count will be 2 (member 105 + node count of 105).
For member 101, left node count will be 1 (member 102) + 5 (total node count of member 102) = 6, right node count will be 2 (member 103 + node count of 103)
any thoughts? any help will be appreciated.