I need to create cartisean join.

In table A I have only one field with a list of numbers (values 1 thru 25)
In table B I have only one field with list of available territories.
How can I join to get them in another hold table with every number associated with every territory?

Is there a join all or outer join, something?
Thanks

I am not a relational person, but in pure FOCUS terms I would do:

  
DEFINE FILE CAR
BLANK/A1 WITH COUNTRY = ' ';
END
TABLE FILE CAR
PRINT COUNTRY
BY BLANK
ON TABLE HOLD FORMAT FOCUS INDEX BLANK
END
JOIN BLANK WITH EID IN EMPLOYEE TO ALL BLANK IN HOLD
DEFINE FILE EMPLOYEE
BLANK/A1 WITH EID = ' ';
END
TABLE FILE EMPLOYEE
PRINT
COUNTRY
BY EID
END

basically create a field containing a blank in on file and hold in a focus file indexing teh balnk field, Join the hold file to the other file on a defined field, also with a blank value, and table it all out.

you want a Cartesian product, it would seem.
file 1
a
b
c
d
file2
1
2
3
4
what you want is
a1
a2
a3
a4
b1
b2
b3
b4
etc.
Is that correct?
Classic use of the world famous IBI "MacGyver technique".
If you go to the regular ibi site, for documentation, you'll see a whole set of articles on MacGyver.
The term "outer join" is a Venn Diagram kind of concept...entirely different animal... not what you're talking about.
eg:
file 1
a
b
c
d
file2
b
c
d
e
result file
a stuff from file 1
b stuff from file 1 and 2
c stuff from file 1 and 2
d stuff from file 1 and 2
e stuff from file 2

An example of the McGyver technique:

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 
ITALY MASERATI 
ITALY PEUGEOT 
ITALY TOYOTA 
ITALY TRIUMPH 
JAPAN ALFA ROMEO 
JAPAN AUDI 
JAPAN BMW 
JAPAN DATSUN 
JAPAN JAGUAR 
JAPAN JENSEN 
JAPAN MASERATI 
JAPAN PEUGEOT 
JAPAN TOYOTA 
JAPAN TRIUMPH 
W GERMANY ALFA ROMEO 
W GERMANY AUDI 
W GERMANY BMW 
W GERMANY DATSUN 
W GERMANY JAGUAR 
W GERMANY JENSEN 
W GERMANY MASERATI 
W GERMANY PEUGEOT 
W GERMANY TOYOTA 
W GERMANY TRIUMPH 

Francis

Why do you have the host file, H0001, extracted to a FOCUS file with an INDEX on the field to be used in the host join?

It seems an unnecessary step as only the join field in the x-referenced file needs to be indexed and the host file can remain in its original format.

Using a DEFINE based join for the original host file, as I showed in my example, would be the more efficient approach.

Alan,

The forum topic title is "joining hold tables" - so I'm joining HOLD tables.

Francis

Please. You are creating a JOIN between two FOCUS files with an unnecessary INDEX on the host which may lead some people to think you need an INDEX on the host to create a join.

Two HOLD files can be joined directly, but not with the data in this topic or to get the required result in this instance.