Selecting hierarchical data using MySQL variable

Question

I have a table where I store information of directories (like in a filesystem). Each directory (named node) has a nodeid and a parentid. I want to retrieve a full path as efficiently as possible. For this, I am trying to make the following queries work, but these return just the first expected row (that is in fact the inner most directory). Can anyone help to get the full result set so that all hierarchical parents of given directory/node are also selected.

The Queries I am currently experimenting with:

SET @var:= 8;
SELECT * , @var:=parentid FROM `app_nodes` WHERE nodeid IN ( @var )

Alternative:

SET @var:= 8;
SELECT * , @var:=parentid FROM `app_nodes` WHERE nodeid=@var

Alternative:

SET @var:= 8;
SELECT * FROM `app_nodes` WHERE nodeid=@var AND (@var:=parentid)
#the parentid of the first parent is '0', so I expect it to end there.

Sample data:

nodeid      parentid
1           0
2           0
3           0
4           0
5           3
6           5
7           0
8           6
9           0
10          0
11          5
12          6
13          11
14          11

Desired output:

nodeid      parentid
3           0
5           3
6           5
8           6

Can anyone please help?

This seems to have been answered nicely here: stackoverflow.com/questions/16513418/… — nlu
– nlu, Commented Jan 31, 2015 at 14:04
Tried it. But same result. Only one row in output: select nodeid, parentid, @pv:=parentid as 'sample_parent' from app_nodes join (select @pv:=8)tmp where nodeid=@pv — Hamid Sarfraz
– Hamid Sarfraz, Commented Jan 31, 2015 at 15:12

Zanon · Accepted Answer · 2015-01-31 18:53:49Z

1

You've missed the need to order your data. Try the following: SQL Fiddle

select t.nodeid, @pv := t.parentid parentid
from (select * from table1 order by nodeid desc) t
join (select @pv := 8) tmp
where t.nodeid = @pv

Output:

| NODEID | PARENTID |
|--------|----------|
|      8 |        6 |
|      6 |        5 |
|      5 |        3 |
|      3 |        0 |

answered Jan 31, 2015 at 18:53

Zanon

31k21 gold badges119 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting hierarchical data using MySQL variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related