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|
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
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