CODEWITHSUNDEEP
sqlsql-serverrecursion
Alessandro
Alessandro

Reputation: 385

traversing a tree upwards

i am looking for the most efficient way to traverse up a tree hierarchy..

i have / will have a table and anticipate millions of records, the tree will not change..

at the moment i am storing a tree string like so

/20/1/1/1/1/1

where 20 is the seed ID..

how to efficiently crawl upwards? I currently have this

select * from reg where tree in ('/20/1/1/1/1','/20/1/1/1','/20/1/1','/20/1','20')

is there an sql command that doesnt require me to split and loop it?

I am using sql server and am aware of the CTE command but does not perform well when there are millions of records.. :/

thanks

Upvotes: 0

Views: 466

Answers (1)

Mikhail
Mikhail

Reputation: 9007

Reverse your comparison!

SELECT * FROM reg WHERE tree='/20/1/1/1/1' OR '/20/1/1/1/1' LIKE CONCAT(tree, "/%");

Good luck

mysql> create table temp_reg (tree varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp_reg values ('/20/1/1/1/1'),('/30/1/1/1'),('/20/1');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from temp_reg where '/20/1/1/1/1' LIKE CONCAT(tree, "%");
+-------------+
| tree        |
+-------------+
| /20/1/1/1/1 |
| /20/1       |
+-------------+
2 rows in set (0.00 sec)

Upvotes: 1

Related Questions