One level recursive search 1

[xwb]

I need to look up the name of the parent node and print the child node's name along with the parent node's name. I'm doing this in SQLite which means I have to work with a very restricted subset. So that I don't breach the NDA that I've signed, I've made up data similar to what I would use.

id,name,parent,
8,Zadok,7,
7,Aaron,4,
6,Moses,4,
5,Judah,1,
4,Levi,1,
3,Simon,1,
2,Reuben,1,
1,Jacob,0,

The query is

select L1.name, L2.name as parent
from lut L1, lut L2
where L1.parent=L2.id

It is fast for 10 items but I'll be dealing with 100s. I'm just wondering whether there is a neater way or even more efficient way of doing this or is this as good as it gets.

 

[PHV]

As you asked in the ANSI SQL forum you may try to avoid a cartesian product (cross join):

SELECT L1.name, L2.name AS parent
FROM lut L1 INNER JOIN lut L2 ON L1.parent=L2.id

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xwb]

Brilliant. Thanks