Archives of the TeradataForum
Message Posted: Tue, 13 May 2003 @ 07:59:23 GMT
| Subj: | | Re: Recursive sql |
| |
| From: | | John Dubery |
At the moment recursive SQL on a table containing parent-child pairs has to be done in the ways that have been suggested. Either you
write a loop to repetitively extract new parent-child rows from the base table and write them to your result set (the SP example) or enhance
your table with some summary data that helps with specific queries (the high level manager example).
But you can store the parent-child relationships in an entirely different way and this makes some queries a lot quicker. See Joe Celko's
book 'SQL For Smarties'. He shows how you can create the implied tree structure much more directly by storing the position in a nested list
that each child would appear at. The drawback is that you have to significantly update the table whenever a parent-child relationship
alters (or is added or deleted) so it's not appropriate for highly dynamic situations.
Regards,
John
| 
