Very perplexing select Problem

Calling all SQL experts!

How can one do a select for this:

I have a table with (for brevity) 2 columns. RecID and ParentID.

All absolute or generation 0 PARENT records will have a ParentID of 0 (zero) but their children will have their parentID pointed to an existing parent's RecID (a parent of any generation).

I want to do a select to list out the parents with all their children and grandchildren under them and then start listing the next parent and all the children and grand children under them etc.

example

         RECID   ParentID
PARENT   1        0
child    2        1 child of p1
child    6        1 child of p1
child    4        6 child of child
child    5        4 child of child of child
PARENT   3        0 next parent of gen 0
child    7        3 child of p3 or RecID 3

etc.. I want to list it in that order using SQL select.

How may I solve this using SQL SELECT and perhaps HAVING?

!!!!IMPORTANT!!!!
Dont assume that RECIDs will be in any order. RECIDs are just assigned by a sequence as they are entered. And children are not guaranteed to have a recID immediately or sequentially next to their parents recID - only that Child recIDs will be greater than the parent's recCIDs since they can only be entered after their parents records are entered.

Any help from the SQL experts and overlords appreciated!! Received on Fri Sep 16 2005 - 16:55:36 CDT