MySQL Nested Sets - How to Find Parent of Node

MySQL Nested Sets - How to find parent of node?

Look at this question. It is similar to yours. I have posted there a query you may need.

SELECT title, (SELECT TOP 1 title 
           FROM tree t2 
           WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt    
           ORDER BY t2.rgt-t1.rgt ASC) AS parent
FROM tree t1
ORDER BY rgt-lft DESC

I hope there is what you need.

For the following table:

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |

it produces the output:

title                | parent
----------------------------------------------
ELECTRONICS          | NULL
PORTABLE ELECTRONICS | ELECTRONICS
TELEVISIONS          | ELECTRONICS
MP3 PLAYERS          | PORTABLE ELECTRONICS
FLASH                | MP3 PLAYERS
CD PLAYERS           | PORTABLE ELECTRONICS
2 WAY RADIOS         | PORTABLE ELECTRONICS
TUBE                 | TELEVISIONS
LCD                  | TELEVISIONS
PLASMA               | TELEVISIONS

Nested Set Query to retrieve all ancestors of each node

First it's important to understand that you have an implicit GROUP BY

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

To make the point more understandable I'll leave out subqueries and reduce the problem to the banana. Banana is the set [10, 11]. The correct sorted ancestors are those:

SELECT "banana" as node, GROUP_CONCAT(title ORDER by `left`)
  FROM Tree WHERE `left` < 10 AND `right` > 11
  GROUP BY node;

The ORDER BY must be in GROUP_CONCAT() as you want the aggregation function to sort. ORDER BY outside sorts by the aggregated results (i.e. the result of GROUP_CONCAT()). The fact that it worked until level 4 is just luck. ORDER BY has no effect on an aggregate function. You would get the same results with or without the ORDER BY:

SELECT GROUP_CONCAT(title)
  FROM Tree WHERE `left` < 10 AND `right` > 11
  /* ORDER BY `left` */

It might help to understand what
SELECT GROUP_CONCAT(title ORDER BY left) FROM Tree WHERE … ORDER BY left does:

Get a selection (WHERE) which results in three rows in an undefined order:
```
("Food")
("Yellow")
("Fruit")
```
Aggregate the result into one row (implicit GROUP BY) in order to be able to use an aggregate function:
```
(("Food","Yellow", "Fruit"))
```
Fire the aggregate function (GROUP_CONCAT(title, ORDER BY link)) on it. I.e. order by link and then concatenate:
```
("Food,Fruit,Yellow")
```
And now finally it sorts that result (ORDER BY). As it's only one row, sorting changes nothing.
```
("Food,Fruit,Yellow")
```

Nested set model query select nodes inside a parent

This will give you what you want:

SELECT
    c.cat_name
FROM
    tree_struc t
JOIN
    tree_struc c
    ON c.lft BETWEEN t.lft + 1 AND t.rgt - 1
LEFT JOIN
    tree_struc a
    ON a.lft BETWEEN t.lft + 1 AND t.rgt - 1
    AND c.lft BETWEEN a.lft + 1 AND a.rgt - 1
WHERE t.cat_name = 'Music' AND a.id IS NULL;

SQL Fiddle

Nested sets how to determine the left and right nodes

I think you really need to work on understanding how the nested set works.

I would read this for the diagram of the model and how new nodes are inserted (the rest is good info, too).
http://falsinsoft.blogspot.com/2013/01/tree-in-sql-database-nested-set-model.html

Note that all leaves are _rgt = _lft + 1. The _lft of the left most leaf is min(select _lft .... where _rgt = _lft + 1).

Combine this knowledge with the path to a node query on that same page and I think you'll have what you need.

An additional resource is the wikipedia page:

http://en.wikipedia.org/wiki/Nested_set_model

get only direct child of node in nested set model

It's described in the document you linked under the heading Find the Immediate Subordinates of a Node. As you can see it's pretty complex. When I use nested sets I always add a parent field as well, because that makes getting the direct children of a node a lot easier.

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

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

MySQL Nested Sets - How to Find Parent of Node