IBM SC41-5210-04 manual Types of joins in a Query for iSeries query

Types of joins in a Query for iSeries query

Specify Type of Join

Type choice, press Enter.
Type of join	1	1=Matched records
		2=Matched records with primary file
		3=Unmatched records with primary file

F3=Exit	F5=Report	F10=Process/previous
F12=Cancel	F13=Layout	F18=Files

There are three types of joins, or three ways that you can select matched or unmatched records from the specified files. You can:

vSelect only records that have matching records in all the specified files.

vSelect all primary records, and include all matching secondary records. Primary records exist in the primary file. The primary file is the first file selected on the Specify File Selections display. Secondary records exist in secondary files. Secondary files are all of the files selected after the primary file on the Specify File Selections display. After the join is done, the record selection tests, if any, are applied to determine which records are selected.

vSelect only the primary records that have one or more unmatched secondary records, and include all the secondary records that also match.

All three types of joins use the same join fields and join tests specified on the Specify How to Join Files display. They also all use the other specifications in the query definition the same way to select records and produce the output.

Use the second join type (type 2) if you want to use every record in the primary file, whether or not it has a matching record in the secondary file(s).

Use the third join type (type 3) if you want to see which records in the primary file do not have matching records in the secondary file(s).

How to join files in a Query for iSeries query

Use the Specify How to Join Files display to specify the join tests needed when more than one file has been selected for your query. Use this display to tell Query how to join (combine) the files and record formats by specifying pairs of fields with a test value; each test value indicates how those fields must compare before Query can select the record. The result of every test in the join specifications must be true before the matching records in each of the files are joined as one record for the record selection tests that follow.

When comparing character values, all values must be marked with compatible CCSIDs. When comparing SBCS character values, all values and any collating sequence to be applied must be marked with compatible CCSIDs.

Chapter 4. Specifying and selecting files for a Query for iSeries query 43