Nested Set SQL Query to retrieve the first ancestor of each node

Question

i have a Nested Set Tree and i am searching for an SQL query to retrieve the first ancestor of each node.

I will always get the root node or a list of all ancestors. See: Nested Set Query to retrieve all ancestors of each node

Sample:

The tree:

ROOT
 - A
   - B 
   - C
     - D
 - E
   - F
     - G
     - H
       - I
 - J
   - K

The needed Output:

|    NODE |  ACESTOR |
|---------|----------|
|       A |        A |
|       B |        A |
|       C |        A |
|       D |        A |
|       E |        E |
|       F |        E |
|       G |        E |
|       H |        E |
|       I |        E |
|       J |        J |
|       K |        J |

UPDATE: Some sample Data:

CREATE TABLE `nstree` (
  `sid` int,
  `left` int,
  `right` int,
  `level` int,
  `name` varchar(255)
);


INSERT INTO `nstree` (`sid`, `left`, `right`, `level`, `name`) VALUES
(1, 1, 24, 0, 'Root'),
(2, 2, 9, 1, 'A'),
(3, 3, 4, 2, 'B'),
(4, 5, 8, 2, 'C'),
(5, 6, 7, 3, 'D'),
(6, 10, 19, 1, 'E'),
(7, 11, 18, 2, 'F'),
(8, 12, 13, 3, 'G'),
(9, 14, 17, 3, 'H'),
(10, 15, 16, 4, 'I'),
(11, 20, 23, 1, 'J'),
(12, 21, 22, 2, 'K');

Will create this Table:

|  sid| left|right|level| name|
|-----|-----|-----|-----|-----|
|    1|    1|   24|    0| Root|
|    2|    2|    9|    1|    A|
|    3|    3|    4|    2|    B|
|    4|    5|    8|    2|    C|
|    5|    6|    7|    3|    D|
|    6|   10|   19|    1|    E|
|    7|   11|   18|    2|    F|
|    8|   12|   13|    3|    G|
|    9|   14|   17|    3|    H|
|   10|   15|   16|    4|    I|
|   11|   20|   23|    1|    J|
|   12|   21|   22|    2|    K|

The SQL query for all ancestors:

SELECT n.sid, n.name, GROUP_CONCAT(p.sid ORDER BY p.left) ancestors 
FROM nstree n
LEFT JOIN nstree p ON p.left < n.left AND p.right > n.right
GROUP BY n.sid;

will produce this result:

|  sid| name|ancestors  
|-----|-----|--------|
|    1| Root|    NULL|
|    2|    A|       1|
|    3|    B|     1,2|
|    4|    C|     1,2|
|    5|    D|   1,2,4|
|    6|    E|       1|
|    7|    F|     1,6|
|    8|    G|   1,6,7|
|    9|    H|   1,6,7|
|   10|    I| 1,6,7,9|
|   11|    J|       1|
|   12|    K|    1,11|

but i need only the first node after root

|  sid| name|ancestor| 
|-----|-----|--------|
|    1| Root|    NULL|
|    2|    A|       2|
|    3|    B|       2|
|    4|    C|       2|
|    5|    D|       2|
|    6|    E|       6|
|    7|    F|       6|
|    8|    G|       6|
|    9|    H|       6|
|   10|    I|       6|
|   11|    J|      11|
|   12|    K|      11|

Please show us the sample data that corresponds to this result. — GMB, Sep 19 '20 at 20:28

score 1 · Accepted Answer · answered Sep 20 '20 at 14:23

As I understand your question, you want to display the level 1 ancestor only. Starting from your existing query, you could just use conditional aggregation:

MAX(CASE WHEN p.level = 1 THEN p.sid END) as l1_ancestor

This is a bit closer to what you ask for, and would produce the result that you want:

CASE WHEN n.level = 1 THEN n.sid ELSE MAX(CASE WHEN p.level = 1 THEN p.sid END) END res l1_ancestor

Demo on DB Fiddle:

sid | name | ancestors | l1_ancestor
--: | :--- | :-------- | ----------:
  1 | Root | null      |        null
  2 | A    | 1         |           2
  3 | B    | 1,2       |           2
  4 | C    | 1,2       |           2
  5 | D    | 1,2,4     |           2
  6 | E    | 1         |           6
  7 | F    | 1,6       |           6
  8 | G    | 1,6,7     |           6
  9 | H    | 1,6,7     |           6
 10 | I    | 1,6,7,9   |           6
 11 | J    | 1         |          11
 12 | K    | 1,11      |          11

Thank you very mutch. This works perfect. I have never seen so a query construct. — Andreas, Sep 22 '20 at 06:51

Nested Set SQL Query to retrieve the first ancestor of each node

1 Answers1