let's take a table called users like this

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:

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

CodePudding user response：

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, '/%')

CodePudding user response：

Correct, normalised way to do it is not via a path, but via board_parent_id. Each member should only store the ID of their direct parent. This data structure forms a hierarchy which you can query in a way that returns a parent, their children, their children etc. using only this one column. This is called a recursive query.

Example:

• Sample data structure

  CREATE TABLE Users(
       id INT,
       name VARCHAR(100),
       board_parent_id INT,
       is_golden BOOL
  );
  
  INSERT INTO Users VALUES
       (1, 'One',   NULL, TRUE),
       (2, 'Two',   NULL, TRUE),
       (3, 'Three', NULL, FALSE),
       (4, 'Four',  1,    FALSE),
       (5, 'Five',  2,    FALSE),
       (6, 'Six',   3,    FALSE),
       (7, 'Seven', 6,    FALSE),
       (8, 'Eight', 2,    FALSE),
       (9, 'Nine',  3,    FALSE)
  

• Query:

  WITH RECURSIVE GoldenPyramid AS (
       SELECT *
       FROM Users
       WHERE is_golden
  
       UNION ALL
  
       SELECT u.*
       FROM Users u
       INNER JOIN GoldenPyramid gp ON gp.id = u.board_parent_id
  )
  SELECT *
  FROM GoldenPyramid
  

You can see it in action in this fiddle: http://sqlfiddle.com/#!17/ae31f/1/0 :)