I'm kind of in a bind right now, I have this Database table with a recursive relation in it pointing to a 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!