0

I have the following schema (was simplified):

users (id, pwd)
users_data (id, user_id, name)
users_parent (parent_user_id, children_user_id)

How can I list the child names given a parent id?

I saw on Google some queries that involves WITH and UNION ALL, but that sentences are a bit new for me.

candlejack
  • 1,189
  • 2
  • 22
  • 51

1 Answers1

2

Assuming the hierarchy is just one level for parents and one level for children, we can try:

SELECT ud.name
FROM users_parent up
INNER JOIN users_data ud
    ON up.children_user_id = ud.user_id
WHERE
    up.parent_user_id = <some value>;

Edit: Based on this SO question I managed to piece together a hierarchical query which should work for your schema:

SELECT children_user_id, ud.name, parent_user_id 
FROM
((SELECT * FROM users_parent
  ORDER BY parent_user_id, children_user_id) parents_sorted,
 (SELECT @pv := '1') initialisation)
LEFT JOIN users_data ud
    ON parents_sorted.children_user_id = ud.user_id
WHERE
    FIND_IN_SET(parent_user_id, @pv) > 0 AND
    @pv := CONCAT(@pv, ',', children_user_id);

Demo

To use the abovr query, just copy it over to Workbench (or whatever tool you are using with MySQL) and then assign the parent_user_id you want to the session variable @pv. The output will contain all children descended from this parent, along with their names.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    That's awesome, a simple `INNER JOIN` :D what about non just one level in hierarchy? Like A is children of B and B is children of C, etc... – candlejack Dec 29 '17 at 05:54
  • 1
    @candlejack It is more difficult, but possible. [See here](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) for more information. – Tim Biegeleisen Dec 29 '17 at 05:56
  • Thanks I will take a look :-) – candlejack Dec 29 '17 at 06:00
  • :-o In the actual answer, what must I add to get *users.id* too? I have put *users.id* on `SELECT` and *users* in `FROM` But I get duplicated values :/ – candlejack Dec 29 '17 at 06:02
  • @candlejack I updated my answer to give a query for a recursive hierarchy. Tested to some degree, but have a look and let us know if there are any problems. – Tim Biegeleisen Dec 29 '17 at 06:14