The task: provide a neat hierarchical tree as a web interface to a project management system where any project might have a variable number of sub-projects. A tree structure in other words. So I ended up with this:
|ID||Project Name||Parent ID|
|1||The Mega Project|
Nice and neat… the only problem is that on the face of it, relational databases aren’t very good at handling trees, and after a bit of experimentation I was ready to write a whole lot of procedural code in ASP to take a recordset and build a hierarchy from it. But of course I googled first. The initial responses included Joe Celko’s (in)famous nested-set tree structure which was far too complicated for my feeble mind to grasp and didn’t look as if it would provide a simple interface for users to add new projects.
And then I came across Philip Greenspun’s interpretation of the classic problem of an employee table in which some employees are managed by others who in turn are managed by others, all the way up to the big boss. He used the Oracle-specific CONNECT BY PRIOR which I’d come across elsewhere, but explained it in a way I could actually understand. Just the ticket! So with a single SQL statement I now had my neat hierarchical recordset.