Select many relational ID in one shot(MYSQL)

Question

so we have:

table users

id name password parent_id

the first user have the id 1, and others have the parent_id 1, so I select all the users that have the parent_id == 1 - they are the childs of the user with 1, okay its all right, but now i need to select the users that have the parent_id of the selected before users with they id, if they exists of course

    user with id 1
   /      |     \
  /       |      \
 /        |       \
users with parent_id 1

user id 2    user id 3 id  user 4
|          |        |
|          |        |
|          |        |

and here is the same, I need to select all the users that have the parent_id 2, 3, 4 for each of those user, its is something like a pyramide(triangle) from the top to the bottom

So the question is how can i make a query that will select it in one shot, not in many queries by extracting the id and then make other query - its not good i think

do you have an idea??

[this](http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html) may help. afair is's not so simple in mysql, but can be made in postgres — k102, Jun 29 '12 at 08:02
MySQL doesn't support recursive functions, so it is not well suited to this adjacency list model of storing hierarchical data. 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, Jun 29 '12 at 08:03

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

Here is a question, that covers your problem:
Is it possible to query a tree structure table in MySQL in a single query, to any depth?

Query below works only for finding children and grand-children of a single user and is a product of misunderstanding the question!

You could try joining user table on itself twice.

SELECT * FROM users as up 
JOIN users as u on up.id=u.parent_id 
JOIN users as uc on u.id=uc.parent_id 
WHERE up.id={$grandParentUserId}

Aliases: up = user's parent, u = user, us = user's child.
Definitely not a pretty solution, but it's a single request.

edited May 23 '17 at 12:27

Community

1
1

answered Jun 29 '12 at 08:07

Niemand

346
2
10

And if there are 4 levels? Or a hundred? – ypercubeᵀᴹ Jun 29 '12 at 08:20
in my case i need to do that in 24 levels – Mister PHP Jun 29 '12 at 08:25
i really didn't understand how to do that, and what is that variable $grandParentUserId ??? – Mister PHP Jun 29 '12 at 08:27
@ypercube, in question there were 3 levels. I thought depth was static. – Niemand Jun 29 '12 at 08:31
@MisterPHP: In that case, you should follow the two links (by k102 and eggyal). The Closure Table approach may be better in your case (your queries will be easier to write for sure, while the Inserts/Updates/Deletes will be a bit more complex than your current design). – ypercubeᵀᴹ Jun 29 '12 at 08:36

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

I see you are using CI. You can have a look at this answer. Somewhat related to your question. You can select the users with NULL parent ID first and then populate their children

https://stackoverflow.com/a/9937130/876117

edited May 23 '17 at 12:27

Community

1
1

answered Jun 29 '12 at 08:38

Broncha

3,794
1
24
34

Select many relational ID in one shot(MYSQL)

2 Answers2