Join of 4 tables 2

[kaeserea]

Hello!

I'd like to join 4 tables with the following structure:

[tt]
|-------------------| |-------------------|
| table1 | | table2 |
|-------------------| |-------------------|
| | | |
| field11----------|-------------------field21 |
| field12 | | |
| | | | |
|-------------------| |-------------------|
|
|
|-------------------| |-------------------|
| table3 | | | table4 |
|-------------------| |-------------------|
| | | | |
| field31 | | |
| field32----------|-------------------field41 |
| | | |
|-------------------| |-------------------|
[/tt]

The relations in words:
[tt]
(field11 of table1) : (field21 of table2) = 1:1
(field12 of table2) : (field31 of table3) = 1:n
(field32 of table3) : (field41 of table4) = m:1
[/tt]

How do I write this in a WebFocus Join without joining 2 table then creating a hold file, then joining the other 2 tables and creating another hold file, and finally joining the 2 hold files?

Best regards,
Eva

 

[focwizard]

JOIN FIELD11 IN TABLE1 TO     FIELD21 IN TABLE2 AS J1.
-* table2 is now part of the table1 structure
JOIN FIELD12 IN TABLE1 TO ALL FIELD31 IN TABLE3 AS J2.
-* table3 is now part of the table1 structure
JOIN FIELD32 IN TABLE1 TO     FIELD41 IN TABLE4 AS J3.

A many-to-one JOIN is just many instances of a one-to-one JOIN.

 

[kaeserea]

Hello focwizard,

thanx a lot. It works fine. Why do I need the "AS J1" ? Where can the join label be used?

Best regards
Eva

 

[focwizard]

The JOIN label is used to selectively clear or re-issue JOINs, as well as in cases of a recursive structure (a file is in the structure more than once), when each instance is not further identified (i.e. through the TAG option).

The DEFAULT 'AS' phrase is ' ' (blank), so if you issued TWO JOINs, without an AS phrase, the second would override the first.