[SOLVED] How would you do this join?

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,

WebFOCUS 7.6
Windows, All Outputs

Posts: 6 | Registered: August 16, 2010

atturhari

Platinum Member

posted

Hide Post

JOIN
LEFT_OUTER ID on A TO ALL ID on B
END

WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML

Posts: 165 | Registered: September 29, 2008

Ignored post by atturhari posted

Show Post

haridis

Member

posted

Hide Post

may all of my problems be solved as easily.

thank you.

WebFOCUS 7.6
Windows, All Outputs

Posts: 6 | Registered: August 16, 2010

Ignored post by haridis posted

Show Post

Danny-SRL

Virtuoso

posted

Hide Post

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
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006

Ignored post by Danny-SRL posted

Show Post

haridis

Member

posted

Hide Post

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

WebFOCUS 7.6
Windows, All Outputs

Posts: 6 | Registered: August 16, 2010

Ignored post by haridis posted

Show Post

Please Wait. Your request is being processed...