Retrieve Hierarchical Data from MySql database

Question

This is my Mysql Database

╔════╦═══════════╗
║ ID ║ Parent_ID ║
╠════╬═══════════╣
║  1 ║         0 ║
╠════╬═══════════╣
║  2 ║         1 ║
╠════╬═══════════╣
║  3 ║         1 ║
╠════╬═══════════╣
║  4 ║         3 ║
╠════╬═══════════╣
║  5 ║         4 ║
╚════╩═══════════╝

What I want to achieve :

When user search for ID 1 , I want to get all those element whose Parent_ID is 1 and also all those IDs whose have 1 as their parent or grand parent or great grand parent and so on.

OR Simply all descendants of ID 1.

Example :

if user search for 1, the algorithm should give result
{2,3,4,5} -- 4,5 because 1 is their great grand parent.
if user search for 2, the algorithm should give empty result { } as no element have 2 as a Parent_ID
if user search for 3, the algorithm should give result {4,5} -- 5 because 3 is its grand parent.

What is the good way of saving and retrieving these type of data from database?

I am using Java and MySQL.

Thanks.

This question in it's present form is a bit too broad. I recommend reading this: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — e4c5, Aug 02 '16 at 08:35
@DavidWallace OP didn't know it has to be recursive, hence the question — Joanvo, Aug 02 '16 at 09:02
@DavidWallace 5 appers because 1 is great grand parent of 5 as (1->2,3), (3->4), (4->5). Hence 1 is great grand parent of 5. — Prakash P, Aug 02 '16 at 09:09
@DavidWallace intend of OP is to retrieve all the descendants. — Prakash P, Aug 02 '16 at 09:14
@PCP if you always query for the root parent, store it in another column (eg: Root_Parent_Id), and you can get all rows with a single query. You can also check this link to go more in depth: https://blogs.msdn.microsoft.com/anthonybloesch/2006/02/15/hierarchies-trees-in-sql-server-2005/ — Joanvo, Aug 02 '16 at 10:52
@Joanvo Could u please explain little bit or if u could write answer it will be appreciated . — Prakash P, Aug 02 '16 at 11:05

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Seeing your data model, you can't do it in a single SQL query, since you need an unknown level of recursion. You need to create a function or stored procedure to iterate over the results of subsequent queries.

This answer may help you: https://dba.stackexchange.com/questions/30021/mysql-tree-hierarchical-query

[Edit]

If you always query for a root parent (i.e. a row that may have children but has no parent), you can store that id in each row and query by that column:

╔════╦═══════════╦════════════════╗
║ ID ║ Parent_ID ║ Root_Parent_ID ║
╠════╬═══════════╬════════════════╣
║  1 ║         0 ║              1 ║
╠════╬═══════════╬════════════════╣
║  2 ║         1 ║              1 ║
╠════╬═══════════╬════════════════╣
║  3 ║         1 ║              1 ║
╠════╬═══════════╬════════════════╣
║  4 ║         3 ║              1 ║
╠════╬═══════════╬════════════════╣
║  5 ║         4 ║              1 ║
╠════╬═══════════╬════════════════╣
║  6 ║         0 ║              6 ║
╠════╬═══════════╬════════════════╣
║  7 ║         6 ║              6 ║
╠════╬═══════════╬════════════════╣
║  8 ║         7 ║              6 ║
╚════╩═══════════╩════════════════╝

Then you can just query: SELECT * FROM mytable WHERE Root_Parent_ID = 1

Otherwise, I recommend you to go more in depth on how to store this data more efficiently: https://blogs.msdn.microsoft.com/anthonybloesch/2006/02/15/hierarchies-trees-in-sql-server-2005/

thank you for taking out your time for answering the question. — Prakash P, Aug 02 '16 at 16:22
at the time of inserting data into the sql table, I only have two information ID and Parent_ID. So are you suggesting, I create a Root_Parent_ID column and insert Root_Parent_ID during the time of creation of every row. — Prakash P, Aug 02 '16 at 16:25
and how to get descendants of element lets say ID = 3. i.e {4,5} as {3->4, 4->5}. While getting the list of descendants of 3, I don't want list of its parents, grand parents. — Prakash P, Aug 02 '16 at 17:55
For each row, set root_parent_id equal to its parent root_parent_id — Joanvo, Aug 03 '16 at 06:31

Retrieve Hierarchical Data from MySql database

1 Answers1