1

I have a table name of users.

It contains following fields.

user_id  |  parent_user_id  | name

Consider my user_id is 5, I want to get the users who have parent_user_id 5 and its children and childrens of children and etc

How to get all children's of particular parent id?

w3father
  • 569
  • 2
  • 11
  • 26
  • 1
    So you mean you want to get everyone who has a id of 5? Give a quick example. – Florin Stingaciu Aug 30 '12 at 14:09
  • 1
    Maybe it's easier to do with php walking the tree? – Andrius Naruševičius Aug 30 '12 at 14:11
  • 4
    MySQL doesn't support recursive functions, so it is not well suited to the adjacency list model of storing hierarchical data (which you describe here). You ought to consider restructuring your data to use either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. – eggyal Aug 30 '12 at 14:11
  • 1
    Look at this: http://stackoverflow.com/a/2782653/259457 – Travesty3 Aug 30 '12 at 14:12
  • yes i want to retrieving data with a hierarchical structure – w3father Aug 30 '12 at 14:18
  • Honestly, most modern developers will use NoSQL data structures to store tree-like data structures. It makes things much simpler. – Mike Brant Aug 30 '12 at 14:19

2 Answers2

0

You cannot do it recursively. You can do it with LEFT JOINs but you have to choose a maximum depth.

SELECT * FROM users u1
LEFT JOIN users u2 ON u1.user_id = u2.parent_user_id
LEFT JOIN users u3 ON u2.user_id = u3.parent_user_id
WHERE u1.user_id = [SOME_USER_ID]

But as Mike Brant suggested you might consider other alternatives such as NoSQL.

Community
  • 1
  • 1
Tchoupi
  • 14,560
  • 5
  • 37
  • 71
0

try something like that.

    SELECT user_id FROM users WHERE parent_user_id = "5" GROUP BY user_id

this will give all childs of the parent id is 5

Scooter Daraf
  • 525
  • 7
  • 23