Larry
THe following example is from my Advanced SQL Queries book. We have an
entire chapter on this subject. Since you're willing to limit your
structure to 4 levels, I have just the select for you!
The two tables structures are:
BillOfMat: (the bill of materials)
ItemNum INTEGER
SubItem INTEGER
Quantity NUMERIC (9,2)
Items (master table of item numbers and meanings):
ItemNum INTEGER
ItemName TEXT 20
This SELECT does a full Bill of Materials explosion to 4 levels, showing the
item names:
It does it for ONE top level parent item, set in a program to variable
vItem.
SELECT T5.ItemName=11,T6.ItemName=14,T7.ItemName=15,T8.ItemName=16,+
T9.ItemName=16 +
FROM BillofMat T1,BillofMat T2,BillofMat T3,BillofMat T4, Items T5, +
Items T6, Items T7, Items T8, Items T9 +
WHERE T1.ItemNum = .vItemNum +
AND T1.ItemNum = T5.ItemNum and T1.SubItem = T6.ItemNum +
AND T1.SubItem = T2.ItemNum and T2.SubItem = T7.ItemNum +
AND T2.SubItem = T3.ItemNum and T3.SubItem = T8.ItemNum +
AND T3.SubItem = T4.ItemNum AND T4.SubItem = T9.ItemNum +
UNION +
SELECT T5.ItemName=11,T6.ItemName=14,T7.ItemName=15,T8.ItemName=16,+
(' ') +
FROM BillofMat T1,BillofMat T2,BillofMat T3, Items T5, +
Items T6, Items T7, Items T8 +
WHERE T1.ItemNum = .vItemNum +
AND T1.ItemNum = T5.ItemNum and T1.SubItem = T6.ItemNum +
AND T1.SubItem = T2.ItemNum and T2.SubItem = T7.ItemNum +
AND T2.SubItem = T3.ItemNum and T3.SubItem = T8.ItemNum +
AND (t3.SubItem NOT IN +
(SELECT t4.ItemNum FROM BillOfMat t4)) +
UNION +
SELECT T5.ItemName=11,T6.ItemName=14,T7.ItemName=15,(' '),+
(' ') +
FROM BillofMat T1,BillofMat T2, Items T5, +
Items T6, Items T7 +
WHERE T1.ItemNum = .vItemNum +
AND T1.ItemNum = T5.ItemNum and T1.SubItem = T6.ItemNum +
AND T1.SubItem = T2.ItemNum and T2.SubItem = T7.ItemNum +
AND (t2.SubItem NOT IN (SELECT t3.ItemNum FROM BillOfMat t3)) +
UNION +
SELECT T5.ItemName=11,T6.ItemName=14,(' '),(' '),+
(' ') +
FROM BillofMat T1, Items T5, +
Items T6 +
WHERE T1.ItemNum = .vItemNum +
AND T1.ItemNum = T5.ItemNum and T1.SubItem = T6.ItemNum +
AND (t1.SubItem NOT IN (SELECT t2.ItemNum FROM BillOfMat t2))
If you want more about this - sample programs, database etc., email me
privately.
David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Lawrence Lustig" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
Sent: Sunday, July 11, 2004 11:43 AM
Subject: [RBASE-L] - Representing a tree in a table.
> I'm dealing with the issue of trying to represent a
> tree in a table. In my case, it's nested Expense
> codes for an accounting system. I've set the table up
> with a self-referencing FK/PK relationship to get the
> parent of any row.
>
> My problem is, what's the easiest and "cheapest" way
> to get the entire parentage of a given child row in
> the table. I'm willing to limit myself to some
> reasonable level of nesting -- say four ancestors for
> a row.
>
> I could do this in a single query if R:Base allowed
> multiple outer joins in the SELECT syntax, but it
> doesn't. My tendency would be to write a stored
> procedure to do sequential queries to get the parents,
> but I think this would be pretty expensive in terms of
> time.
>
> I recall a discussion of this issue some time ago.
> Anyone have any suggestions?
> --
> Larry
>
> --- RBASE-L
> ================================================
> TO POST A MESSAGE TO ALL MEMBERS:
> Send a plain text email to [EMAIL PROTECTED]
>
> (Don't use any of these words as your Subject:
> INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
> REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message SUBJECT, put just one word: INTRO
> ================================================
> TO UNSUBSCRIBE:
> Send a plain text email to [EMAIL PROTECTED]
> In the message SUBJECT, put just one word: UNSUBSCRIBE
> ================================================
> TO SEARCH ARCHIVES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message SUBJECT, put just one word: SEARCH-n
> (where n is the number of days). In the message body,
> place any
> text to search for.
> ================================================
>
>
