Help with properly displaying data

[flepkows]

I have a recursive database design between two tables package table and parts table. These are setup as follows:

Part(PartID(PK),PartNumber)
Package(PackageID(PK),ParentID,ChildID)

The partID is linked to the parentID(FK to Part table) and I am using aliases to be able to get info like the part number for in elements that are nested four or five levels in. So for example:

Part->Package(linked via elements Part-ID->ParentID), then Package->Part1(alias to part table)(linked via elements ChildID->PartID)

I repeat the link between the last two tables above depending on how many levels of nesting of parts I want to support. Here is my problem: I have a crystal report that I group by part number from each alias of the part table in the report. This way it shows a stepped and indendent hierarchy of part structures. Say for instance I have the above setup but with one more level of nesting, if I have a record in the db that has nesting to only one level and not the same numer as my report, the record is not displayed. It seems like it will only display records that have the same levels of nesting as the report supports, and if records have more nesting it cuts the nesting off when it reaches the deepest point it can support. I want to be able to workaround this problem, so that if I have my alias tables setup to handle four levels of nesting and I have a record with three levels I want to be able to display just the three and a blank for the fourth child value. I would really appreciate any help, I have been banging my head on the wall over this. Thanks in advance!

 

[synapsevampire]

Recursive tables are a bit difficult to work with, and the thought of displaying a blank for something that doesn't exist might be tricky from within Crystal as it isn't blank, it's null.

You might be better served to get the data within a Stored Procedure or View, depending upon your version of Crystal and the database used.

Here's a MySQL whitepaper on the subject:

http://www.seas.ucla.edu/db2/db2s0/frame3.htm

-k