MYSQL SELECT LOOP dynamically

Question

I'm a newbie in mysql and not sure if possible to have a loop statement in select.

My Table:

ID      user_id     parent
1       13          2
2       14          2
3       15          13
4       16          13
5       17          14
6       18          14
7       19          15
8       20          15

parent with value of 2 has no parent. it is something like root user_id is equivalent to child

so if i do

SELECT * FROM my_table WHERE parent = 2

basically output is:

ID      user_id     parent
1       13          2
2       14          2

is there a way i can get the other child? should i do subquery? If 2 is selected this is the image i want to achieve: http://awesomescreenshot.com/04b2y7qfe2

What is the other child? What do you expect the output to be? — univerio, Jun 09 '14 at 17:00
If `SELECT * FROM my_table WHERE parent = 2` it will get all the table entries, parent col will connect to user_id. something like this [link](http://awesomescreenshot.com/04b2y7qfe2) — user3723195, Jun 09 '14 at 17:04
You want to do a recursive select? See [this answer](http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql) for details. In your case, your `parent` column would be `col1` and your `user_id` column would be `col3`. — univerio, Jun 09 '14 at 17:08

CMPS · Answer 1 · 2014-06-09T17:13:49.320

Here's how you can connect your parents users with their children users:

This will display only record that have parents:

SELECT c.user_id, c.parent, p.user_id, p.parent
FROM my_table c, my_table p
WHERE c.parent = p.user_id

Or using left Join, display records that have or have no parents (the ones with 2 as parentID)

SELECT c.user_id as ChildID, c.parent as ChildParentID, p.user_id as ParentID, p.parent as ParentOfParentID
FROM my_table c
LEFT JOIN my_table p
ON c.parent = p.user_id

Alex · Answer 2 · 2014-06-09T17:15:24.243

This looks like what is often called an adjacency tree list, where a tree structure is defined in list form by specifying IDs and parent IDs, with a given parent ID value indicating the root node of the tree (in this case, the value 2).

Amir's answer will give you the immediate children of each node. If you'd like to retrieve entire branches of the tree, from a given node, you could look at Modified Preorder Tree Traversal (MPTT). You just fetch all rows where the left field value falls between the root node's left and right values. The key drawback with this method is that on average 50% of the records in the table need to be updated when adding or removing nodes from the tree. If you've got a big table, that can be a bit of a performance hit.

Unfortunately, as far as I know, MySQL doesn't have a way of performing recursive queries, which would be another way of solving this problem. Some other database systems of this functionality, but not MySQL.

MYSQL SELECT LOOP dynamically

2 Answers2