UserID UserName ParentID TopID
1 abc Null Null
2 edf 1 1
3 gef 1 1
4 huj 3 1
5 jdi 4 1
6 das 2 1
7 new Null Null
8 gka 7 7
TopID and ParentID is from the userID
I Want to get a user record and its child and subchild record. Here userid1 is the root and its child are userid2 and userid 3. So If the user id is 1 I have to display all the records from userid 1 to userid 6 since all are child and SUbchild of the root. Similarly for userid3 I have to display userid3 and its child Userid 4 and Child of Userid 4 Userid5 if the userid is 3
output should be
Userid Username
3 gef
4 huj
5 jdi
I will know the userid and the topID so how can I do the query to acheive the above result.
SELECT UserID, UserName FROM tbl_User WHERE ParentID=3 OR UserID=3 And TopID=1;
By the above query I am able to display userid 3 and userid 4 I am not able to display userid 5, Kind of struck in it. Need help. Thanks
It is technically possible to do recursive hierarchical queries in MySQL using stored procedures.
Here is one adapted to your scenario:
Time to point out some caveats:
Since this is recursively calling a stored procedure, you need to increase the size of max_sp_recursion_depth, which has a max value of 255 (defaults to 0).
My results on a non-busy server with the limited test data (10 tuples of the
user
table) took 0.07-0.10 seconds to complete. The performance is such that it might be best to put the recursion in your application layer.I didn't take advantage of your
TopID
column, so there might be a logic flaw. But the two test-cases gave me the expected results.Disclaimer: This example was just to show that it can be done in MySQL, not that I endorse it in anyway. Stored Procedures, temporary tables and cursors are perhaps not the best way to do this problem.
This is one of the best articles I've seen for explaining the "Modified Preorder Tree Traversal" method of storing tree-like data in a SQL-style database.
http://www.sitepoint.com/hierarchical-data-database/
The MPTT stuff starts on page 2.
Essentially, you store a "Left" and a "Right" value for each node in the tree, in such a manner that to get all children of
ParentA
, you get the Left and Right for ParentA, thenWell not a pretty clean implementation but since you need only the children and sub-children, either of these might work:
Query1:
Query 2:
EDIT 1: Alternatively, you may modify your table structure to make it more convenient to query all children of a particular category. Please follow this link to read more on storing hierarchical data in MySQL.
Also, you may think on storing your data hierarchically in a tree-like fashion that is very well explained in this article.
Please note that each method has its trade-offs with respect to retrieving desired results vs adding/removing categories but I'm sure you'll enjoy the reading.
To get all of the parents of the selected child (user_id = 3 in this example):
To get all of the children of a selected user_id
This requires the following function
END
This example heavily uses session variables which many sql users may be unfamiliar with, so here's a link that may provide some insight: session variables