How to link multiple tables using a JOIN

If all of t hese are 1:n to the next, this will do it:

FOR EACH function-c NO-LOCK, 
 EACH Func-Setup WHERE Func-Setup.function-id = function-c.function-id AND Func-Setup.function-id = Func-Mail.function-id,
EACH 3rd where 3rd.function-id = function-c.function-id no-lock:

Is that the case?

May be good to use no-lock on every level. Otherwise it will use a shared lock and this is mostly unwanted and makes query slower.

Klaus

Klaus, your query will probably generate too many records. You probably need outer-join:

DEFINE TEMP-TABLE tt1 
   FIELD cc AS CHAR
   FIELD ii AS INT
   .

DEFINE TEMP-TABLE tt2 
   FIELD cc AS CHAR
   FIELD ii AS INT
   .

DEFINE TEMP-TABLE tt3
   FIELD cc AS CHAR
   FIELD ii AS INT
   .

CREATE tt1. tt1.cc = "A".
CREATE tt2. tt2.cc = "A".
CREATE tt3. tt3.cc = "A".
CREATE tt3. ASSIGN tt3.cc = "A" tt3.ii = 1.
CREATE tt1. tt1.cc = "B".
CREATE tt3. tt3.cc = "B".

DEFINE QUERY qq FOR tt1, tt2, tt3.

OPEN QUERY qq 
   FOR EACH tt1, 
   EACH tt2 OUTER-JOIN WHERE tt2.cc = tt1.cc, 
   EACH tt3 OUTER-JOIN WHERE tt3.cc = tt1.cc
   .

GET FIRST qq.
      
DO WHILE AVAILABLE tt1:

   MESSAGE
      tt1.cc tt1.ii SKIP
      tt2.cc tt2.ii SKIP
      tt3.cc tt3.ii
   VIEW-AS ALERT-BOX.


   GET NEXT qq.
END.

Hi guys, many thanks for the suggested solutions.

@Klaus , i think your suggestions is type of format im looking for

@Stefan , the records im looking to retrieve are only over a 3 day period, so the output will not be large

however, when compiling Klaus's suggestion solution, i get an error informing

"missing FOR, FIND or CREATE for a table with function-id in the curent block" ... the error is pointing at line

AND Func-Setup.function-id = Func-Mail.function-id,

EACH Func-Mail WHERE Func-Mail.function-id = function-c.function-id NO-LOCK:

Kind Regards

Eoin

My issue is not with the number of records, my issue is that the results can be incorrect.

A -> B -> C

is a different relationship than

A -> B

A -> C

FOR EACH A, EACH B OF A, EACH C OF B will:

1. only return records when A, B AND C exist.

2. will return A records repeated B times C times

3. will return B records repeated C times

Without further information on how A, B and C relate to each other I can only guess.

Hi guys,

I think the relationship between the tables is the most important aspect we miss here.

For example:

a) If we have a relationship 1 : 1 : 1. then the things are quite simple:

FOR EACH A

,FIRST B OF A

,FIRST C OF A...

....

So the first question is what is the relation between these?

All in all, I think you meant the following:

FOR EACH function-c NO-LOCK,

           FIRST Func-Setup WHERE Func-Setup.function-id = function-c.function-id NO-LOCK,

           FIRST Func-Mail WHERE Func-Mail.function-id = function-c.function-id NO-LOCK:

               CREATE tt-funcsetup.

               ASSIGN

                  tt-funcsetup.func-id          = Func-Setup.function-id

                  tt-funcsetup.func-name        = function-c.function-name

                  tt-funcsetup.dir              = Func-Setup.dir

                  tt-funcsetup.runs             = Func-Setup.multiple-run

                  tt-funcsetup.last-run-date    = Func-Setup.run-date

                  tt-funcsetup.emailed          = Func-Mail.sendto.

                  RELEASE tt-funcsetup

       END.

Let me know if this helps.

Regards,

Gabriel

There is a lot of guessing going on here.  

Stefan, you are certainly right that the two relationship structures are different, but here in the OP's original code we can see that all three of the tables contain a common field, function-id.  This *suggests* that we might not really have parent child relationships here at all, but rather three parallel tables which presumably deal with a different domain, but where each is indexed by a common key.

However, what we don't know is whether this is a unique key on these tables or not.   The absence of any first or last type logic in the original suggests that *either*

1) There is a single record in each table for any given function-id; or

2) The OP wants every record from the each table that contains the same function-id.

Given the *EACH* in the original code, my *guess* would be the 2nd.   This might mean that there is a sort of parent child relationship in that one of the tables defines the thing and the other two relate to events? about that thing.  I.e., one would guess function-c defines the function and Func-Setup relates to one or more setup functions about that function and Func-Mail relates to one or more mail events about that function.   ***Lots*** of guessing there based simply on the names.  What makes me question that is the use of Func-Setup to set the function-id in the TT ... yes, they are all the same, but best practice would seem to be to set it from the parent.

Based on all that guessing, I will tentatively suggest that what you want is

for each Function-C no-lock,
    each Func-Setup no-lock where Func-Setup.Function-ID = Function-C.Function-ID,
    each Func-Mail no-lock where Func-Mail.Function-ID = Function-C.Function-ID: