SQL: select all depending nodes from a list of parent

Question

let's take a table called users like this

id	name	...	path
22	John	...	2/8/11/22/
23	Mark	...	1/3/9/15/21/23/

where the path rapresents the hierarchy parent-child.

Now, I have a list of "special users" which we can call them "board-users" and, for semplicity, let's say they have id 1 to 10.

I would like to select all the rows which derive from the board users (including board users rows itself) and I would like to add a column related to the board users parent; something like this:

id	name	...	path	board_parent_id
1	Stephany	...	1/	1
2	Karl	...	2/	2
...	...	...	...	...
83	Lucy	...	4/11/43/51/69/73/83/	4

I have tried something like

SELECT u1.id as board_parent_id, u2.*
FROM USERS AS u1
CROSS JOIN USERS AS u2
WHERE u1.id = '1'
AND u2.path LIKE '%1%'
UNION
SELECT u1.id as board_parent_id, u2.*
FROM USERS AS u1
CROSS JOIN USERS AS u2
WHERE u1.id = '2'
AND u2.path LIKE '%2%'
UNION
...

but honestly I believe this is a very stupid way to do this

Answer 1

First observation:

• your current query should search for '%/1/%' not '%1%'
• the latter will match '63/41/999/', when you don't want it to

But that also means that your path should start with '/'

• or concat '/' to the start in your query

If you know the list of golden id's, then it's just a join?

WITH
  golden(id) AS
(
  VALUES
    (1),(2),(3)...,(n)
)
SELECT
  golden.id   AS board_parent_id,
  users.*
FROM
  golden
INNER JOIN
  users
    ON CONCAT('/', user.path) LIKE CONCAT('%/', golden.id, '/%')

Answer 2

Thank to the comments and other answers I finally got my query done.

I post here an example

WITH BOARD AS(
   SELECT * FROM users 
   WHERE ID IN ('id_1', 'id_2', 'id_3', 'id_4'...)
)
SELECT BOARD.id AS board_id,
u.*
FROM users AS u 
INNER JOIN BOARD ON u.path LIKE CONCAT('%',BOARD.id.'%')

This way, it seems to work the way I wanted