Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join of 4 tables 2

Status
Not open for further replies.
kaeserea

kaeserea

Programmer
Joined
Feb 26, 2003
Messages
164
Location
DE
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
 
Sort by date Sort by votes
Code: 
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ekl0908
  • Locked
  • Question Question
Can I create a JOIN which uses two different keys depending on a field in the file?
Replies
0
Views
475
ekl0908
ekl0908
sxschech
  • Locked
  • Question Question
Prompt Based on List Of Values
Replies
0
Views
330
sxschech
sxschech
tracisell
  • Locked
  • Question Question
Left outer join error
Replies
2
Views
469
FrankExp
FrankExp
ekl0908
  • Locked
  • Question Question
Table of quantities by week-ending date should include weeks with zero
Replies
2
Views
486
jimster06
jimster06
LeonelSanchezJr
  • Locked
  • Question Question
Page# of Page## - Problem w/ Coordinated Compound Reports
Replies
1
Views
435
focwizard
focwizard

Part and Inventory Search

Sponsor

Back
Top