Let’s look at the join options in more detail. Changing these options can result in totally different data being returned to the report and it is important to understand each one. Table 10-2 shows a list of the different linking options and how they affect the resulting data.
Table 10-2. Join Options
| Join Type | Result Set Description |
|---|---|
| Inner Join | Records from the left table are matched with records from the right table. Only records with an exact match are included. |
| Left Outer Join | All records from the left table are included. Records from the right table are only included if there is a matching record between the left and right tables. If the table on the left doesn’t have a matching record with the right table, then the missing fields are filled with NULLs. |
| Right Outer Join | All records from the right table are included. Field values from the left table are included if there is a matching record in the left table. NULL values are stored in the missing fields if there is no matching record in the left table. |
| Full Outer Join | Every record from both tables is included. When records from both tables match, the fields in the new result set are filled in as normal. The other fields are set to NULL when there is no matching record for one of the tables. |
To see how each join type works, let’s look at two simple tables and see what data is returned for each type of join. The two tables are Product and Product Type. We will only look at a few records in each table for illustration purposes. They are linked together by the field Product Type ID. Since both tables have a field called Product Type ID, I use the letter P to designate fields that come from the Product table and the letters PT to designate the Product Type table.
| Table 10-3. Product Table (P) | Table 10-4. Product Type Table (PT) | Product ID |
|---|---|---|
| Product Name | Product Type ID | 1 |
| Helmets | 10 | 2 |
| Tires | 12 | 3 |
| Grease | 13 | |
| Product Type ID | Product Type Name | |
| 10 | Accessories | |
| 11 | Clothing | |
| 12 | Parts |
An inner join displays the records from both tables when there is a matching record between the two tables. In this sample, only two records have a matching Product Type ID.
Table 10-4. Inner Join
| P.Product ID | P.Product Name | P.Product Type ID | PT.Product Type ID | PT.Product Type Name |
|---|---|---|---|---|
| 1 | Helmets | 10 | 10 | Accessories |
| 2 | Tires | 12 | 12 | Parts |
Both tables have one record missing from the result set. The Product table is missing the last record because the right table doesn’t have a matching Product Type ID of 13. The Product Type table is missing the second record because the Product table doesn’t have a record with a Product Type ID of 11.
The left outer join uses all tables from the left table, but only uses records from the right side when there is a match.
Table 10-5. Left Outer Join
| P.Product ID | P.Product Name | P.Product Type ID | PT.Product Type ID | PT.Product Type Name |
|---|---|---|---|---|
| 1 | Helmets | 10 | 10 | Accessories |
| 2 | Tires | 12 | 12 | Parts |
| 3 | Grease | 13 | NULL | NULL |
The three records are all from the Product table. The last record in this result set has NULL in the last two columns because the Product Type table doesn’t have an ID of 13 in it.
The right outer join uses all tables from the right table, but the only records it uses from the left side are the ones with a match.
Table 10-6. Right Outer Join
| P.Product ID | P.Product Name | P.Product Type ID | PT.Product Type ID | PT.Product Type Name |
|---|---|---|---|---|
| 1 | Helmets | 10 | 10 | Accessories |
| NULL | NULL | NULL | 11 | Clothing |
| 2 | Tires | 12 | 12 | Parts |
The three records are all from the Product table. The second record in this result set has NULL columns from the Product table because it doesn’t have an ID of 11 in it.
The full outer join selects all the records from both tables. If there isn’t a matching record, it fills in NULL for the missing fields.
Table 10-7. Full Outer Join
| P.Product ID | P.Product Name | P.Product Type ID | PT.Product Type ID | PT.Product Type Name |
|---|---|---|---|---|
| 1 | Helmets | 10 | 10 | Accessories |
| NULL | NULL | NULL | 11 | Clothing |
| 2 | Tires | 12 | 12 | Parts |
| 3 | Grease | 13 | NULL | NULL |
If you compare this table to the two previous tables, it’s as if the full outer join combines the results of left outer join with the results of the right outer join.
Now that you have an understanding of how different join types affect the result set that Crystal Reports uses to build a report, let’s look at the last button on the Links tab. The Order Links button sets the order in which the links between the tables are created. This is used only when there is more than one link shown. The tables will be linked automatically, in the order that they are shown in the dialog box. The default linking order processes the links on the left before the links on the right. Changing the default linking order is useful when there is a hierarchy of tables joined together and the order to which they are joined is important. This can happen when you are using a link to return a subset of records from two tables, and these records are then linked to another table. Changing the order of the links changes the resulting data.
