Situation:
I have a SQL Table that is self linking.  Records act as parent and child.  In this case, it is for a packaging system where it is possible to have multiple packages containing a commodity (product).  For example, a pallet containing 50 boxes, each box contains 24 bags of potato chips.  The application allows for an unlimited number of package to package relationships.

Here's a sample diagram from SQL Server.

Problem:
The customer requires a report where only the commodity is displayed.  The commodity may be burried very deep in the chain of packages.  And because the commodity is only aware of the immediate package above it (using the example above, the boxes), getting to the commodity can be difficult.  To help illustrate, here's a perfectly valid example from the application.




This is a very complex pallet to say the least.  Probably one that would never happen, but it is still "valid" as far as the application is concerned.

The report requirements state that, given the CargoLineId (represented by the green ball, pallet), list the commodities.  The report is not interested in displaying any details about the packaging.

Solution:
Using the self-join of table BookingPackage we can determine the immediate children of each record.  The child package will have a ParentBookingPackageId that matches the parent BookingPackageId.  Each package may or may not contain a commodity.  What we must do is walk our way down the tree of packages to get to the commodities.

The solution presented here could be used differently depending on the solution required.  Use the example as a guide and change it to suit your particular needs.

Here is the code in T-SQL:
/*
Create a temporary table to hold all PackageIds.
*/
DECLARE @PackageIdList TABLE (
    PackageId int )

/*
Create a temporary table to hold the CommodityLineIds.
This table eventually contains every Commodity attached
to the booking and is used as the result set.
*/
DECLARE @CommodityIdList TABLE (
    CommodityLineId int )

/*
Insert the top level packages
@BookingId is passed in as a parameter
*/
INSERT INTO @PackageIdList
SELECT BookingPackageId
FROM BookingPackage pkg
    INNER JOIN BookingCargoLine cargo on (cargo.BookingCargoLineId = pkg.BookingCargoLineId)
WHERE
    cargo.BookingId = @BookingId

/*
Get a count of second level packages
*/
DECLARE @pkgCount int
SET @pkgCount = (
    SELECT COUNT(BookingPackage.BookingPackageId)
    FROM BookingPackage
    INNER JOIN @PackageIdList list on (list.PackageId = BookingPackage.ParentBookingPackageId)
    WHERE BookingPackage.BookingPackageId NOT IN (SELECT PackageId FROM @PackageIdList)
    )

WHILE @pkgCount > 0
BEGIN
    /*
    Insert the package list into the temporary Package table
    If the PackageId is already collected into the temporary table,
    do not add it.
    With each pass, sub-packages are added to the list.
    */
    INSERT INTO @PackageIdList
    SELECT BookingPackage.BookingPackageId
    FROM BookingPackage
    INNER JOIN @PackageIdList list on (list.PackageId = BookingPackage.ParentBookingPackageId)
    WHERE BookingPackage.BookingPackageId NOT IN (SELECT PackageId FROM @PackageIdList)

    /*
    We just added packages to the list, check to see if there are more to add.
    Eventually, we will get to the lowest level of sub-packages and
    the count will be zero; ending the WHILE loop.
    */
    SET @pkgCount = (
        SELECT COUNT(BookingPackage.BookingPackageId)
        FROM BookingPackage
        INNER JOIN @PackageIdList list on (list.PackageId = BookingPackage.ParentBookingPackageId)
        WHERE BookingPackage.BookingPackageId NOT IN (SELECT PackageId FROM @PackageIdList)
        )
END

/*
Now that we have a list packages, we can derive the
Commodities that belong to each one.
*/
INSERT INTO @CommodityIdList
SELECT BookingCommodityId
FROM BookingCommodity
WHERE BookingPackageId IN (SELECT PackageId FROM @PackageIdList)
/*
Now we have a table of commodities that we can use in the rest of
the procedure as a joining table to retrieve the commodity details
for the report.
*/
*** END OF T-SQL

The secret to this working is the WHILE loop and the table @PackageIdList being used as a filter to determine the depth of the package tree.  We are inserting into the @PackageIdList table and, at the same time, using it as an inner join for parent packages.  The where clause is used to make sure we don't pump duplicate values into the table.

    INSERT INTO @PackageIdList
    SELECT BookingPackage.BookingPackageId
    FROM BookingPackage
    INNER JOIN @PackageIdList list on (list.PackageId = BookingPackage.ParentBookingPackageId)
    WHERE BookingPackage.BookingPackageId NOT IN (SELECT PackageId FROM @PackageIdList)

The final step is to fill a table variable, @CommodityIdList, with the list of commodity ids belonging to the main cargo line, or package.

     INSERT INTO @CommodityIdList
     SELECT BookingCommodityId
     FROM BookingCommodity
     WHERE BookingPackageId IN (SELECT PackageId FROM @PackageIdList)

If we wanted, we could have put a lot more information into this table.  I decided to use it as a joining table to get the details I need later on in the procedure.

Conclusion:
We end up with a result that mirrors recursion in T-SQL.  We didn't have to use cursors or extra procedures to make it work.  Now, if we were only using SQL Server 2005, we could achieve the same result with one statement using the WITH clause.  I'll find a good example and post on that next.