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,

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

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!

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.

Thanks Ginny!

Now its clear to me.