What is the format for joining several files at once? I know how to do a simple one file join to another file, but how would you join field A in File 1 to Field B in file 2 and to Field C in file 3 and to Field in file 4?

I looked in my FOCUS manuals, but could only find simple joins, plus I tried searching on the net.

Hopefully, someone will be able to help...thanks in advance!

This message has been edited. Last edited by: webmeister,

How would you do that in SQL?

I think that can't be done either, if you can do it on the database side and create a view. Then create a master on the view and build your report directly on that master.

What you can do is a chained join or a join like field A in fileX to field B in fileY and field C in fileX to field D in fileZ.
But then you have a multipath join and these joins can only be used as descriptive fields (translation of the codes).

JOIN FIELD_A IN FILE1 TO [ALL] FIELD_B IN FILE2 AS J1
JOIN FIELD_A IN FILE1 TO [ALL] FIELD_C IN FILE3 AS J2
JOIN FIELD_A IN FILE1 TO [ALL] FIELD_D IN FILE4 AS J3
  

Frank and Ginny,

Thanks for your replies..... Frank, I wasn't quite clear on your reply...Ginny, I will use your suggestion. It's rough when you've been away from FOCUS since 1997, but a lot of it is coming back quickly.

As an addendum to Ginny's excellent simple example, if by some chance the files have fields with the same names.

JOIN FIELD_A IN FILE1 TO FIELD_A IN FILE2 AS J1
JOIN FILE2.FIELD_A IN FILE1 TO FIELD_A IN FILE3 AS J3
JOIN FILE3.FIELD_A IN FILE1 TO FIELD_D IN FILE4 AS J4

Don't forget about the TAG attribute. Using Leah's example:

JOIN FIELD_A   IN FILE1 TAG A TO FIELD_A IN FILE2 TAG B AS J1
JOIN B.FIELD_A IN FILE1       TO FIELD_A IN FILE3 TAG C AS J3
JOIN C.FIELD_A IN FILE1       TO FIELD_D IN FILE4       AS J4

Thanks for the addition, when you reply at 10:36 at night, some code just goes away from current memory.