The first thing we need to worry about is how to get our data out of the database. A novice would probably start off with something like this:
SELECT ItemID, Name, ParentID FROM Stuff ORDER BY ParentID, ItemID
This makes the assumption that all of the items were entered in the order that they should come back out. This is very rarely the case. Similarly, this won’t work:
SELECT ItemID, Name, ParentID FROM Stuff ORDER BY ParentID, Name
All that gains us is that we now know that we’re almost in alphabetical order. Somewhat. But really, our same problem from last time comes up: we just can’t trust the IDs to be in any sort of order. We really need to be ordering by the Name.
A more SQL-savvy coder might then try this:
SELECT s1.ItemID, s1.Name, s1.ParentID FROM Stuff AS s1 LEFT OUTER JOIN STUFF AS s2 ON (s1.ParentID = s2.ItemID) ORDER BY s2.Name, s1.Name
A join has been added, so that we’re now ordering by the Parent’s name. This will work just fine … if we’re limited to only one level of parent-child relationships. Given our test data, we know that we’re not. Trying to out-guess the user and add more joins is only going to get us into more trouble (and slow down the query).
Trying to do parent-child relationships in straight SQL is next to impossible given the open-ended scenario we’ve got here. There are solutions if your DB engine supports parent-child queries natively, as well as solutions that use stored procedures. But what if you don’t have access to either of those?
For now, let’s just get the data out in the order we’d like to see it if it had no parent-child relationships:
SELECT ItemID, Name, ParentID FROM Stuff ORDER BY Name
Next time we’ll look at how we can re-sort the query with ColdFusion.