How to use JOINs and How to retrieve data by using different JOINs.

JOINS

Created two tables  1^st table: StudentHeader and 2^nd table: StudentPaymentDetails,

Added Fields and inserted the values in both tables,

Added the relations as well,

Created Job for Joins(Inner Join, Outer Join, Exists Join, Not-exists Join).

Note: while writing joins use "While select * from TableName".

StudentHeader: 

StudentPaymentDetails:

Inner Join: It will shows the data from parent and child which is matched in both table. It won’t show the not matched records.

 

X++ code Input:  

Output:

Outer join: It retrieves the data from the both matching records and not matched records from the parent table

 

X++ code:

Output:

Exists Join: It retrieves the records from the parent table only if there exist record in the child table it will stops looking after one match in the child table. 

X++ code:        

Output:

Not-exists Join: Not-exists Join shows the data only from parent table which does not have match with the child table.

X++ code: 

Output:

Hope this info is useful to you....🙂

Thanks for the Visit....👍