Count Multi-level marketing (tree) record in java mysql

Question --->
          

users table In the Registration time every user have to put parent_id , who registration under parent_id, so i made different table for it sponser table after that make tree like that and i want to count record like that so plz guide me how can i count record like that or there is any way i mean to say for this kind of counting i have to change in database , thanks in advance Answer----->>>  for hierarchical data storing you can see Nested Set Model. see in detail http://en.wikipedia.org/wiki/Nested_set_model. hope it will perfectly serve your purpose. Which show Below  Nested set model                          The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases. The term was apparently introduced by Joe Celko; others describe the same technique without naming it [1] or using different terms.                            Example In a clothing store catalog, clothing may be categorized according to the hierarchy given on the left: A hierarchy: types of clothing     The numbering assigned by tree traversal The resulting representation Node Left Right Clothing 1 22 Men's 2 9 Women's 10 21 Suits 3 8 Slacks 4 5 Jackets 6 7 Dresses 11 16 Skirts 17 18 Blouses 19 20 Evening Gowns 12 13 Sun Dresses 14 15     The "Clothing" category, with the highest position in the hierarchy, encompasses all subordinating categories. It is therefore given left and right domain values of 1 and 22, the latter value being the double of the total number of nodes being represented. The next hierarchical level contains "Men's" and "Women's", both containing levels within themselves that must be accounted for. Each level's data node is assigned left and right domain values according to the number of sublevels contained within, as shown in the table data. Performance Queries using nested sets can be expected to be faster than queries using a stored procedure to traverse an adjacency list, and so are the faster option for databases which lack native recursive query constructs, such as MySQL.[4] However, recursive SQL queries can be expected to perform comparably for 'find immediate descendants' queries, and much faster for other depth search queries, and so are the faster option for databases which provide them, such as PostgreSQL,[5] Oracle,[6] and Microsoft SQL Server.[7] Drawbacks Nested sets are very slow for inserts because it requires updating lft and rgt for all records in the table after the insert. This can cause a lot of database thrash[citation needed] as many rows are rewritten and indexes rebuilt. The Nested interval model does not suffer from this problem, but is more complex to implement, and is not as well known. The nested interval model stores the position of the nodes as rational numbers expressed as quotients (n/d). [1] Variations Using the nested set model as described above has some performance limitations during certain tree traversal operations. For example, trying to find the immediate child nodes given a parent node requires pruning the subtree to a specific level as in the following SQL code example: SELECT Child.Node, Child.LEFT, Child.RIGHT FROM Tree AS Parent, Tree AS Child WHERE Child.LEFT BETWEEN Parent.LEFT AND Parent.RIGHT AND NOT EXISTS ( -- No Middle Node SELECT * FROM Tree AS Mid WHERE Mid.LEFT BETWEEN Parent.LEFT AND Parent.RIGHT AND Child.LEFT BETWEEN Mid.LEFT AND Mid.RIGHT AND Mid.Node NOT IN (Parent.Node AND Child.Node) ) AND Parent.LEFT = 1 -- Given Parent Node Left Index The query gets even more complicated when searching for children more than one level deep. To overcome this limitation and simplify Tree traversal an additional column is added to the model to maintain the depth of a node within a tree. The resulting representation Node Left Right Depth Clothing 1 22 0 Men's 2 9 1 Women's 10 21 1 Suits 3 8 2 Slacks 4 5 3 Jackets 6 7 3 Dresses 11 16 2 Skirts 17 18 2 Blouses 19 20 2 Evening Gowns 12 13 3 Sun Dresses 14 15 3     In this model, finding the immediate children given a parent node can be accomplished with the following SQL code: SELECT Child.Node, Child.LEFT, Child.RIGHT FROM Tree AS Child, Tree AS Parent WHERE Child.Depth = Parent.Depth + 1 AND Child.LEFT > Parent.LEFT AND Child.RIGHT < Parent.RIGHT AND Parent.LEFT = 1 -- Given Parent Node Left Index