[SOLVED] Difference between JOIN and JOIN ALL

Hi,

I am confused bteween JOIN and JOIN ALL.
I looked in help, it says JOIN(without ALL) is unique join and with ALL is non-unique(one to many).
But what if I have multiple records in cross-reference table corresponding to a record in master table. Do I need to use JOIN or JOIN ALL.

I know its a stupid question, but just got stuck at it.

Thanks!

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

Thanks and Regards,
Piscian

WebFocus 714
Windows Server 2003
Enterprise Edition
HTML,Excel,PDF

Posts: 15 | Registered: June 04, 2007

Shrikant

Silver Member

posted

Hide Post

Piscian,

You need to use JOIN ALL. For instance consider two tables, DEPARTMENT and EMPLOYEE, since there can be many employees in a department, to retrive all the employee details of any department, you need to use JOIN ALL, if only one record is to be extracted, JOIN will do that.

JOIN DEPT_ID IN DEPARTMENT TO ALL DEPT_ID IN EMPLOYEE AS JOIN11

Ensure that the table EMPLOYEE is sorted by DEPT_ID before attempting this join, else sort the data in a temporary hold file and use here.

-Shrikant

FOCUS 7.2.3
Platform: IBM system Z9 Business class
O/P formats: Flat files, excel and CSV files

Posts: 39 | Location: Hyderabad, India | Registered: April 28, 2007

Ignored post by Shrikant posted

Show Post

Piscian

Member

posted

Hide Post

Thanks Shrikant!

But I am not able to get the difference.
If I use JOIN here, will it match just one record in cross-reference file for a record in host file and ignore all other matching records?

Which means we should use JOIN only when key to be joined is primary key in both the tables.

Please correct me if I am wrong.

Thanks!

Thanks and Regards,
Piscian

WebFocus 714
Windows Server 2003
Enterprise Edition
HTML,Excel,PDF

Posts: 15 | Registered: June 04, 2007

Ignored post by Piscian posted

Show Post

GinnyJakes

Expert

posted

Hide Post

JOIN ALL does a one to many join. JOIN does a one to one join. If you do the latter against a structure where there can be multiple children for each parent, you will only get the first one. To get them all, you would need to do the former.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Show Post

Piscian

Member

posted

Hide Post

Thanks Ginny!

Now its clear to me.