Pass values from a Select to CROSS JOIN CTE in MySQL

Question

I'm having a table in MySQL database namely "users"

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `parent_user_id` int
);

INSERT INTO `users` (`user_id`, `name`, `parent_user_id`) VALUES
(1, 'John', null), 
(2, 'Emma', 1), 
(3, 'Watson', 2),
(4, 'Peter', 3), 
(5, 'Rose', 1), 
(6, 'Harry', 4),
(7, 'Jim', 6), 
(8, 'Jack', 5), 
(9, 'Josh', 8),
(10, 'Jem', 9);

I need to query the table users that top most ancestors not the root user (i.e. root user is 1)

Expected Output:

user_id     name          root_parent_user_id     root_parent_user_name
______________________________________________________________
1           John          null               null
2           Emma          1                  John
3           Watson        2                  Emma
4           Peter         2                  Emma
5           Rose          1                  John
6           Harry         2                  Emma
7           Jim           2                  Emma
8           Jack          5                  Rose
9           Josh          5                  Rose
10          Jem           5                  Rose

Logic for the Requirement:

Case #1 - IF Parent Id is NULL:

• The user has the parent_id null, so this is the very first user and it don't have any parent / ancestor

Case #2: - IF Parent Id is 1 (which means the user was created by the very first ancestor)

• The user has the parent_id 1, so we need to take the parent 1

Case #3: - IF Parent Id is N (which means the user was created by Nth child of Case #2)

• The user has the parent_id n (for example: user_id is 6 and n is 4) so we need to find the root parent and which was created by the very first ancestor and finally the answer is "user_id : 2" (i.e., Emma).

I tried the following code:

SELECT * FROM users USR
CROSS JOIN (
WITH RECURSIVE cte (user_id, name, parent_user_id) AS
    (
      SELECT user_id, name, parent_user_id
        FROM users
        WHERE user_id = USR.user_id
      UNION ALL
      SELECT c.user_id, c.name, c.parent_user_id
        FROM cte AS cp JOIN users AS c
          ON cp.parent_user_id = c.user_id
    )
    SELECT * FROM cte where parent_user_id = 1
) PUSR ON 1 = 1;

I through an error Error Code: 1054. Unknown column 'USR.user_id' in 'where clause'

Kindly assist me how to pass a value inside CTE where condition from the above query and how to achieve the requested output.

Answer 1

I don't think this is the best approach as it will cost you a lot in processing & could become a nightmare to maintain later on for your application. You should simply store the root user for each user in database (that's the way most tree systems handle this). Your table structure would become : user_id | name | parent_user_id | root_user_id

If you really want to persist in this way, I would suggest you to check this out (you would just have to add your "non user 1 root" condition to it): https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7150