Linking table to itself until all levels are found

[Guest]

I am creating a Bill of Materials application using a table that stores
parent/child relationships.

tblBOM (tableName)
Relationship_Key
Parent_ID
Child_ID
Child_Qty

The relationship can go several levels deep. I've implemented a treeview
and it works as expected. But now I have to output more data. I have to be
able to sum up quantities and determine how many can be built with current
inventory or how many of each to buy to build a requested quantity.

This can easily be accomplished with a query linking the appropriate number
of tblBOM to Parent -> Child as deep as the relationships go. However, this
is inefficient since you do not know how deep any given BOM is.

Is there a way using code to add tblBOM and then add tblBOM_1, link on
Parent (left join) to Child_1, then add tblBOM_2.... and so on until there
are no more records in the child field?

Thnks for the help
aWs

 

[Allen Browne]

This is always messy. The SQL language doesn't handle recursion well, and
there is always the possibility of infinite recursion--usually bad data,
where a record is its own parent at some level.

Whenever I have had to do these, I've cheated by specifying a finite depth
that the records must resolve in. Then I walk the records in VBA code, and
resolve them into a temp table. If they don't resolve in the number of
levels supported, or if you find an infinite recursion, the code opts out
and tells the user to fix the problem and try again. Once the temp table
reflects all data, it besomes very simple to use to process the tasks
instead of trying to process the job in recursive queries.

There's a very simple SQL only solution to just 4 levels in this link:
http://allenbrowne.com/ser-06.html

If that approach is not suitable, Joe Celko has written several articles on
handling it in SQL, so you could follow his ideas. For a starting point:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

HTH

 

[Guest]

Thanks Allen,

I will go over these articals to see if they help.

aWs