Hi all,

I need to join 3 tables in what I believe is known as a Cartesian Product or from reading other posts I have seen this known as the McGyver technique!

The objective is to get a final table containing every record from table A, and for each of these records to have every record from table B, and also for both those sets to get every record from table C.

I found a great example of joining 2 tables that Francis had provided in this thread:

SET HOLDLIST=PRINTONLY
-RUN

DEFINE FILE CAR
DUMMY/A1 WITH COUNTRY = '';
END
-RUN

TABLE FILE CAR
SUM
COUNTRY
BY DUMMY
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY
END
-RUN

TABLE FILE CAR
SUM
CAR
BY DUMMY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN DUMMY IN H001 TO ALL DUMMY IN H002 AS J1
-RUN

TABLE FILE H001
PRINT
COUNTRY
CAR
BY COUNTRY NOPRINT
BY CAR NOPRINT
END
-RUN

Gives you this:

COUNTRY CAR
ENGLAND ALFA ROMEO
ENGLAND AUDI
ENGLAND BMW
ENGLAND DATSUN
ENGLAND JAGUAR
ENGLAND JENSEN
ENGLAND MASERATI
ENGLAND PEUGEOT
ENGLAND TOYOTA
ENGLAND TRIUMPH
FRANCE ALFA ROMEO
FRANCE AUDI
FRANCE BMW
FRANCE DATSUN
FRANCE JAGUAR
FRANCE JENSEN
FRANCE MASERATI
FRANCE PEUGEOT
FRANCE TOYOTA
FRANCE TRIUMPH
ITALY ALFA ROMEO
ITALY AUDI
ITALY BMW
ITALY DATSUN
ITALY JAGUAR
ITALY JENSEN
Etc
etc


So I hoped I could just add a 3rd file (H003 containing MODELS) to this logic as so:

SET HOLDLIST=PRINTONLY
-RUN

DEFINE FILE CAR
DUMMY/A1 WITH COUNTRY = '';
END
-RUN

TABLE FILE CAR
SUM
COUNTRY
BY DUMMY
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY
END
-RUN

TABLE FILE CAR
SUM
CAR
BY DUMMY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY
END
-RUN

TABLE FILE CAR
SUM
MODEL
BY DUMMY
BY MODEL
ON TABLE HOLD AS H003 FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN DUMMY IN H001 TO ALL DUMMY IN H002 AS J1
-RUN

JOIN DUMMY IN H001 TO ALL DUMMY IN H003 AS J2
-RUN

TABLE FILE H001
PRINT
COUNTRY
CAR
MODEL
-*BY COUNTRY NOPRINT
-*BY CAR NOPRINT
-*BY MODEL NOPRINT
END
-RUN

But this creates null fields in the CAR column and also caused null fields when trying to implement into my work environment:


COUNTRY CAR MODEL
ENGLAND ALFA ROMEO B210 2 DOOR AUTO
ENGLAND AUDI COROLLA 4 DOOR DIX AUTO
ENGLAND BMW DORA 2 DOOR
ENGLAND DATSUN INTERCEPTOR III
ENGLAND JAGUAR TR7
ENGLAND JENSEN V12XKE AUTO
ENGLAND MASERATI XJ12L AUTO
ENGLAND PEUGEOT 100 LS 2 DOOR AUTO
ENGLAND TOYOTA 2000 GT VELOCE
ENGLAND TRIUMPH 2000 SPIDER VELOCE
ENGLAND . 2000 4 DOOR BERLINA
ENGLAND . 2002 2 DOOR
ENGLAND . 2002 2 DOOR AUTO
ENGLAND . 3.0 SI 4 DOOR
ENGLAND . 3.0 SI 4 DOOR AUTO
ENGLAND . 504 4 DOOR
ENGLAND . 530I 4 DOOR
ENGLAND . 530I 4 DOOR AUTO
FRANCE ALFA ROMEO B210 2 DOOR AUTO
FRANCE AUDI COROLLA 4 DOOR DIX AUTO
FRANCE BMW DORA 2 DOOR
FRANCE DATSUN INTERCEPTOR III
FRANCE JAGUAR TR7
FRANCE JENSEN V12XKE AUTO
FRANCE MASERATI XJ12L AUTO
FRANCE PEUGEOT 100 LS 2 DOOR AUTO
FRANCE TOYOTA 2000 GT VELOCE
FRANCE TRIUMPH 2000 SPIDER VELOCE
FRANCE . 2000 4 DOOR BERLINA
FRANCE . 2002 2 DOOR
FRANCE . 2002 2 DOOR AUTO
FRANCE . 3.0 SI 4 DOOR
FRANCE . 3.0 SI 4 DOOR AUTO
FRANCE . 504 4 DOOR
FRANCE . 530I 4 DOOR
FRANCE . 530I 4 DOOR AUTO
ITALY ALFA ROMEO B210 2 DOOR AUTO
ITALY AUDI COROLLA 4 DOOR DIX AUTO
ITALY BMW DORA 2 DOOR
ITALY DATSUN INTERCEPTOR III
etc
etc

The only way method I have found to get around this, is to write the results of the first two joins into a hold file, including DUMMY and then to do my 3rd join over this new hold file, as so:

SET HOLDLIST=PRINTONLY
-RUN

DEFINE FILE CAR
DUMMY/A1 WITH COUNTRY = '';
END
-RUN

TABLE FILE CAR
SUM
COUNTRY
BY DUMMY
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX DUMMY
END
-RUN

TABLE FILE CAR
SUM
CAR
BY DUMMY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX DUMMY
END
-RUN

TABLE FILE CAR
SUM
MODEL
BY DUMMY
BY MODEL
ON TABLE HOLD AS H003 FORMAT FOCUS INDEX DUMMY
END
-RUN

JOIN DUMMY IN H001 TO ALL DUMMY IN H002 AS J1
-RUN

TABLE FILE H001
PRINT
COUNTRY
CAR
DUMMY
ON TABLE HOLD AS H_HOLD
END
-RUN

JOIN CLEAR *

JOIN DUMMY IN H_HOLD TO ALL DUMMY IN H003 AS J1
-RUN

TABLE FILE H_HOLD
PRINT
COUNTRY
CAR
MODEL
-*BY COUNTRY NOPRINT
-*BY CAR NOPRINT
-*BY MODEL NOPRINT
END
-RUN

Result:

COUNTRY CAR MODEL
ENGLAND ALFA ROMEO B210 2 DOOR AUTO
ENGLAND ALFA ROMEO COROLLA 4 DOOR DIX AUTO
ENGLAND ALFA ROMEO DORA 2 DOOR
ENGLAND ALFA ROMEO INTERCEPTOR III
ENGLAND ALFA ROMEO TR7
ENGLAND ALFA ROMEO V12XKE AUTO
ENGLAND ALFA ROMEO XJ12L AUTO
ENGLAND ALFA ROMEO 100 LS 2 DOOR AUTO
ENGLAND ALFA ROMEO 2000 GT VELOCE
ENGLAND ALFA ROMEO 2000 SPIDER VELOCE
ENGLAND ALFA ROMEO 2000 4 DOOR BERLINA
ENGLAND ALFA ROMEO 2002 2 DOOR
ENGLAND ALFA ROMEO 2002 2 DOOR AUTO
ENGLAND ALFA ROMEO 3.0 SI 4 DOOR
ENGLAND ALFA ROMEO 3.0 SI 4 DOOR AUTO
ENGLAND ALFA ROMEO 504 4 DOOR
ENGLAND ALFA ROMEO 530I 4 DOOR
ENGLAND ALFA ROMEO 530I 4 DOOR AUTO
ENGLAND AUDI B210 2 DOOR AUTO
ENGLAND AUDI COROLLA 4 DOOR DIX AUTO
ENGLAND AUDI DORA 2 DOOR
ENGLAND AUDI INTERCEPTOR III
ENGLAND AUDI TR7
ENGLAND AUDI V12XKE AUTO
ENGLAND AUDI XJ12L AUTO
ENGLAND AUDI 100 LS 2 DOOR AUTO
ENGLAND AUDI 2000 GT VELOCE
ENGLAND AUDI 2000 SPIDER VELOCE
ENGLAND AUDI 2000 4 DOOR BERLINA
ENGLAND AUDI 2002 2 DOOR
ENGLAND AUDI 2002 2 DOOR AUTO
ENGLAND AUDI 3.0 SI 4 DOOR
ENGLAND AUDI 3.0 SI 4 DOOR AUTO
ENGLAND AUDI 504 4 DOOR
ENGLAND AUDI 530I 4 DOOR
ENGLAND AUDI 530I 4 DOOR AUTO
ENGLAND BMW B210 2 DOOR AUTO
ENGLAND BMW COROLLA 4 DOOR DIX AUTO
ENGLAND BMW DORA 2 DOOR
ENGLAND BMW INTERCEPTOR III
ENGLAND BMW TR7
ENGLAND BMW V12XKE AUTO
ENGLAND BMW XJ12L AUTO
ENGLAND BMW 100 LS 2 DOOR AUTO
ENGLAND BMW 2000 GT VELOCE
ENGLAND BMW 2000 SPIDER VELOCE
ENGLAND BMW 2000 4 DOOR BERLINA
ENGLAND BMW 2002 2 DOOR
ENGLAND BMW 2002 2 DOOR AUTO
ENGLAND BMW 3.0 SI 4 DOOR
ENGLAND BMW 3.0 SI 4 DOOR AUTO
PAGE 2

COUNTRY CAR MODEL
ENGLAND BMW 504 4 DOOR
ENGLAND BMW 530I 4 DOOR
ENGLAND BMW 530I 4 DOOR AUTO
ENGLAND DATSUN B210 2 DOOR AUTO
ENGLAND DATSUN COROLLA 4 DOOR DIX AUTO
ENGLAND DATSUN DORA 2 DOOR
ENGLAND DATSUN INTERCEPTOR III
ENGLAND DATSUN TR7
etc
etc


This gives the desired result, but I wondered if there was a way to avoid the additional hold file between the 2nd and 3rd joins?

Many thanks

Mark

mark
no where do i see SET CARTESION = ON
in your code.
look that up.
-s