nested sql & msaccess

[tim_s]

say i've got an ms access table like this...

Id (AutoNumber)
ParentId (Number)
Name (Text)

with the following data...

1 0 About Us
2 0 Contact Us
3 2 Contact details
4 2 Contact form
5 0 Products

is is possible to produced something like the below with a sql statement.

Level Name
0 About Us
0 Contact Us
1 Contact details
1 Contact form
0 Products

?

I can do if using a recursive function in my programming language but I'd rather do it in sql as it'll be a lot quicker.

Any advice would be much appreciated.

[pdV6]

Your data doesn't make it clear exactly what you're after, but I'm guessing you want a tree-like structure for constructing a menu (or somesuch).

This may well be possible in SQL, but it would be a complete nightmare; I'd be tempted to do it in the program that uses the data to make life easier for myself!

[LaurenceFrost]

I understand what you want to do. Look at the SELF JOIN clause. That will do what you want it to, but I'm unsure if Access supports it.