All-

I'm new to focus and trying to write my first report.

the main point is that i'm experiencing issues joining table a and table b.

Please follow this example

1. Table A looks like this:
(ID, and Name)

001Joe Smith
002Jane Smith
003Mike Smith

Table B looks like this:

(ID, color)

001Red
001Green
001Blue
003Red

2. Now i have define on table B for a counter

were i to try to sum the counts by ID on table b by itself with the following:

SUM(CNTR)
ACROSS Color
BY ID
AS ''

This prints out

001 3
003 1

but i want the sum for table 2... so i join and print the tables together with the define. by

3. joining table a and table b by either way

JOIN
ID on A TO ALL ID on B
END

SET ALL ON;

or i join it this way

JOIN
ID on A to MULTIPLE ID on B

and then

PRINT
ID NAME COLOR

gives

001Joe SmithRed
001Joe SmithGreen
001Joe SmithBlue
003Mike SmithRed

(where's Jane?)

4 sadly, both ways gives me this output when i do a sum across:

Joe Smith 3
Mike Smith 1

5. What's the right join to get this output

Joe Smith 3
Jane Smith .
Mike Smith 1

Thanks kindly for your help,

Drew

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

JOIN
LEFT_OUTER ID on A TO ALL ID on B
END

may all of my problems be solved as easily.

thank you.

Drew,

From this thread, I gather that you are joining 2 relational tables, hence the LEFT_OUTER option.
If you join 2 FOCUS files then you would use your initial syntax. However, please notice:

 
JOIN
ID IN A TO ALL ID IN B AS J1 {IN, not ON; use AS joinname}
END

SET ALL ON {WITHOUT A ;}
 

Daniel-

I tried the following join:

-*----------------------------------------------
-*
-* 5 Join the Files
-*
-*______________________________________________

JOIN CLEAR *

JOIN
BUYER_NO IN UD0 TO ALL
NEXT_ID IN UD1 AS J5
END

SET ALL ON;

UD0 and UD1 stand for User Defined 0 & 1. They are referencing hold files, not fomented as focus.

It did not do a left outer join.

thanks

drew