Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Recursive relationship

Recursive relationship

Recursive relationship

Hi everyone,

I'm kind of in a bind right now, I have this Database table with a recursive relation in it pointing to a parentID.


pkID        Description        ParentID
  1           xxxxxxx            null
  2           xxxxxxx            null
  3           xxxxxxx             2
  4           xxxxxxx            null
  5           xxxxxxx             2
  6           xxxxxxx             3

What I'm trying to do is return a recordset with the data in order so I can populate a HTML select object with it and have proper indentation.  I've gotten it to work in a fairly ugly way using two queries.  One where I get all the parentID=null.  Then I print all of them and do another query getting the items where the parentID match...the recursivity make this VERY slow for large table.

The second query is a modified version of this one where I get all the childNodes -->
SELECT T1.PARENTID, T1.pkID, T1.Description AS childNode, T2.Description AS parentNode
FROM TableItem As T1 LEFT JOIN TableItem As T2 ON T1.PARENTID = T2.pkID
WHERE (((T1.PARENTID) Is Not Null))
ORDER BY T1.PARENTID, T1.pkID, T1.Description;

If I remove the Is Not Null, I get all the records but not in order...wish I was more proficient with SQL in time like these :)

Any help or pointers would be greatly appreciated!


RE: Recursive relationship

If Oracle, use the CONNECT BY command. If you are unsure of how to use CONNECT BY, do a search in the Oracle forum(s).

For other databases, see FAQ183-5322

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RE: Recursive relationship

Sweet! That worked...now I got the record in order.  

Now I could process them with some VB code and sort them alphabetically, but it sure would be nice if I could sort them directly in the query.

I've played with ORDER BY but if I do it on Description I loose the logical tree order and my records aren't sorted in any logical way for the Primary key (element 1 can be Zozo, element 5 can be Batman, element 10 can be Robin [parent 5 - Batman], element 11 - Abraham

I would like to print:

I'm definitly picking up a SQL book to learn more about this stuff...so far I've found two by Joe Celko (SQL for Smarties serie).  Any better recommendations?


P.S. I didn't mean to offend anyone who believe Robin to be superior to Batman...

RE: Recursive relationship


Browsed some more and found the statement I was looking for: ORDER SIBLINGS BY [Clause]

Figured I would post it here so other poster who search for this topic can find it.

Thanks a lot for your help johnherman.

RE: Recursive relationship

Sometimes you have to use group by and order by together.

If you do not like my post feel free to point out your opinion or my errors.

RE: Recursive relationship

Hi Ceh,

Thanks for the reply, why would GROUP BY be used for simple table where no "group" statistics are needed?

Not sure if I can think of an example where I would need summaries while preserving my Tree like view...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close