Get all children from a given parent in mysql

Question

I would like to retrieve all children from a given parent (and optionally their level depth). Also, if possible, I would like to get it in a single query or store procedure.

Something like this solution by Mark Byers, only in reverse direction.

My table structure is like this:

id      parent
1       0
2       1
3       0
4       1
5       2
6       5
7       2
8       4

so i f want the children for 1 I'll get:

+2
 ++5
  +++6
 ++7
+4
 ++8

if want the children for 2 I'll get:

 ++5
  +++6
 ++7

and if want the children for 4 I'll get:

+8

Thanks in advance!

EDIT 1: nested set model is no good to me

Where r u getting 1,4 from? I mean which col ur referring to.. — Teja, Mar 14 '12 at 20:03
Try with this link http://stackoverflow.com/questions/16391660/retrieve-hierarchy-from-a-table-like-this/16392049#16392049 — Meherzad, May 10 '13 at 20:35

score 0 · Answer 1 · answered Mar 14 '12 at 21:27

0

One suggestion is to ditch this adjacency model and change your structure to a nested set. Nested sets are great for fast selecting and removes the need for any recursive structures. However it does come with a price, inserts, updates and deletes are more painful. I did write a blog post on this a while ago and it is MsSql based but it will port to MySql easily. Google also throws up some good posts.

Another suggestion is to load all the rows from the database and then write a recursive method in your front end code and cache the output. I use this approach if I only have a couple of hundred rows in my table.

answered Mar 14 '12 at 21:27

Rippo

22,117
14
78
117

1

In my opinion, nested sets are a poor workaround that is only needed if the DBMS in use doesn't support recursive queries. – May 10 '13 at 21:13
Depends on lots of factors, if you are really concerned with performance as you have a large recordset, then IMO nested sets wins hands down. Is it most definitely NOT a poor workaround. – Rippo May 11 '13 at 09:27
Also this makes an interesting read, http://jsimonbi.wordpress.com/2011/03/01/sql-hierarchy-comparative-performance-2/ but remember you choose the model that sits right with you. – Rippo May 11 '13 at 09:32

score -3 · Answer 2 · edited Oct 17 '16 at 07:53

Following Query will give you all the child tables for a table in MySQL.

Give database name and table name as parameter

SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
       REFERENCED_TABLE_SCHEMA = "database"
AND    REFERENCED_TABLE_NAME = "table";

Get all children from a given parent in mysql

2 Answers2