Join of 4 tables
Join of 4 tables
(OP)
Hello!
I'd like to join 4 tables with the following structure:
|-------------------| |-------------------|
| table1 | | table2 |
|-------------------| |-------------------|
| | | |
| field11----------|-------------------field21 |
| field12 | | |
| | | | |
|-------------------| |-------------------|
|
|
|-------------------| |-------------------|
| table3 | | | table4 |
|-------------------| |-------------------|
| | | | |
| field31 | | |
| field32----------|-------------------field41 |
| | | |
|-------------------| |-------------------|
The relations in words:
(field11 of table1) : (field21 of table2) = 1:1
(field12 of table2) : (field31 of table3) = 1:n
(field32 of table3) : (field41 of table4) = m:1
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
I'd like to join 4 tables with the following structure:
|-------------------| |-------------------|
| table1 | | table2 |
|-------------------| |-------------------|
| | | |
| field11----------|-------------------field21 |
| field12 | | |
| | | | |
|-------------------| |-------------------|
|
|
|-------------------| |-------------------|
| table3 | | | table4 |
|-------------------| |-------------------|
| | | | |
| field31 | | |
| field32----------|-------------------field41 |
| | | |
|-------------------| |-------------------|
The relations in words:
(field11 of table1) : (field21 of table2) = 1:1
(field12 of table2) : (field31 of table3) = 1:n
(field32 of table3) : (field41 of table4) = m:1
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
RE: Join of 4 tables
CODE
-* 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.
RE: Join of 4 tables
thanx a lot. It works fine. Why do I need the "AS J1" ? Where can the join label be used?
Best regards
Eva
RE: Join of 4 tables
The DEFAULT 'AS' phrase is ' ' (blank), so if you issued TWO JOINs, without an AS phrase, the second would override the first.