2

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?

Hamid Sarfraz
  • 1,089
  • 1
  • 14
  • 34
  • 2
    This seems to have been answered nicely here: http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – nlu Jan 31 '15 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 Jan 31 '15 at 15:12
  • 1
    Heres the sqlfiddle: http://sqlfiddle.com/#!2/74f457/2 – Hamid Sarfraz Jan 31 '15 at 15:22

1 Answers1

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 |
Zanon
  • 29,231
  • 20
  • 113
  • 126