"tree" of information - how SQL?

[mfacer]

hi guys...

I have a sort of tree style information structure..... .eg:

Admin
  |- User 1 
  |- User 2
     |- User 3
        |- User 4
        |- User 5
     |- User 6
     |- User 7
  |- User 8
  |- User 9

Each user has a "parent" field..... for example, user 3's parent is user 2. User 4's parent is user 3 and so on...

I am not sure how to get these from the DB without massive loops... I was thinking something like

is it possible to display them like the above..... nested sort of?

I am guessing some code like

if user has any children, do another SQL to get them..... if not, move on to the next parent....
eg:

select from users where (select count() from users where parent = userID))

I'm pretty stuck with this!! THanks for any help!

[tha_mink]

You could use the group by statement to group the parents and then run a loop to print the children. It's not the quickest way or the most efficient but if you don't have millions of users, you won't notice the performance hit.

You could also select all the users, and based on their parent/child status, throw them into seperate arrays or a matrix to print out after the records are pulled and sorted. That wouldn't be a db performance hit and would be easy to develop on the code side.

I suppose it would be helpful to see your data model, where I suspect the real root of the problem lies.