Hmm
DId my posting yesterday not show up? Here it is again:
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
David Blocker
[EMAIL PROTECTED]
781-784-1919
Fax: 781-784-1860
Cell: 339-206-0261
----- Original Message -----
From: "Troy Sosamon" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
Sent: Monday, July 12, 2004 10:13 AM
Subject: [RBASE-L] - RE: Join ?
> Not using an outer join.
> You can only do a single outer join in one statement.
>
> There might be a way to do it using 3 select statements and 2 unions to
get
> it all in one command, but it would not be easy. It would look something
> like this:
>
>
> create OneView as
> Select
> t1.t1_id,
> t1.t1_select,
> t2.t2_id,
> t3.t3_id
> from table1 t1, table2 t2, table3 t3 where t1.t1_id = t2.t2_id and
t1.t1_id
> = t3.t3_id
> Union
> Select
> t1.t1_id,
> t1.t1_select,
> t2.t2_id,
> ' '
> from table1 t1 where t1_id = t2_id and t1_id not in (select t3_id from
> table3)
> Union
> Select
> t1.t1_id,
> t1.t1_select,
> ' ',
> T3_t3_id
> from table1 t1, table2 t3 where t1.t1_id = t3.t3_id
> .............
>
>
> This is not quite complete. You would need to join all 3 tables, and then
> pick up all of the other rows where table2 didn't have anything, and then
> where table3 didn't have anything and then where neither table2 and table3
> did not have a match
>
> Troy
>
>
> Troy
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fogelson,
> Steve
> Sent: Monday, July 12, 2004 7:07 AM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - RE: Join ?
>
> Troy,
>
> Thanks for the response. Is there a way to combine these together?
>
> Steve
>
> -----Original Message-----
> From: Troy Sosamon [mailto:[EMAIL PROTECTED]
> Sent: Sunday, July 11, 2004 6:17 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - RE: Join ?
>
>
> You are looking for the same thing Larry was.
> You need to use 2 view doing left outer joins.
>
>
> Create view1 as
> Select
> t1.t1_id,
> t1.t1_select,
> t2.t2_id
> from table1 t1 left outer join table2 t2 on t1.t1_id = t2.t2_id
>
>
>
> create view2 as
> Select
> v1.t1_id,
> v1.t1_select,
> v2.t2_id,
> t3.t3_id
> from view1 v1 left outer join table3 on v1.t1_id = t3.t3_id
>
>
> view2 should have what you are looking for.
>
>
> Troy
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fogelson,
> Steve
> Sent: Sunday, July 11, 2004 2:09 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - Join ?
>
> Witango 5.0, R:Tango 5.0, R:Base 6.5++
>
> I have 3 tables as follows:
>
> table 1
> t1_id1 PK
> t1_id2
> t1_select
>
> table 2
> t2_id PK
> t1_id1 FK
> t2_start
> t2_end
>
> table 3
> t3_id PK
> t1_id2 FK
> t3_start
> t3_end
>
> I want to end up with a view with the following:
> t1.t1_id
> t1.t1_select
> t2.t2_id (null if no matching key in table2)
> t3.t3_id (null if no matching key in table3)
>
> I want to select all records from table 1 where t1_select=xxx even if
there
> isn't a foreign key match in table 2 and/or table 3, but if there is, I
> would like to include t2.t2_id and/or t3.t3_id in the results.
>
> Matches from table 2 and table 3 depend on todays date >= t2_start and <=
> t2_end and like wise on table 3.
>
> I don't think a join will work as it will not include rows from table 1 if
> no matching keys from table 2 and table 3.
>
> Is this possible?
>
> Thanks
>
> Steve Fogelson
> Internet Commerce Solutions
>
