Recursive relationship 1

[JayF82]

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!

Thanks.

 

[johnherman]

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

 

[JayF82]

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:
Abraham
Batman
Robin
Zozo

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?

Thanks!

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

 

[JayF82]

Okie!

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.

 

[ceh4702]

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.

 

[JayF82]

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...