Join Robin Hunt for an in-depth discussion in this video Understand the different join types, part of Crystal Reports 2013: Linking, Nulls, and Letter Writing.
- Let's learn about relationships in the joins and how they affect our reporting data. It's important to know that an inner join is a default join, and an outer join, when you adjust to that, will produce different data on the reporting canvas. Let's start with a blank report. For example, we'll use Customer and Credits. I'll select Customer first, and then Credit. People often ask, Does it matter which order you select the tables? When you're defining left and right outer joins, it does matter.
The first one you select is considered the left. I'll go to the Links tab. Anytime you have two tables, you always have to have a link to have valid results. Customer Credit ID is the link field between the two tables, and the link, if we click it, we can go look at the Link Options. We see the three standard Join Types: Inner, Left Outer, Right Outer. There are times when you use a Full Outer Join, which would be information on both sides of both tables, typically used for troubleshooting.
You also have options for Enforce Join, and this is particular to SQL and commands you might write. Then for some data sources, you might use the Link Type, but most of us stick with Inner, Left Outer, and Right Outer joins. For example here, this report will show only customers who have credits. I'll drag Customer Name to the Detail section. From the Credit table, I'll use the Credit Authorization Number, and also the Amount.
Then I'll go preview my report. Now I see a list of customers who actually have credits. What I don't see is all customers, regardless of their credit amounts. In order to see every customer, I have to adjust the joins to show everything from the Customer table and if they have an amount in the Credits table. Let's go to the Database, to the Database Expert, and back to the Links tab.
I'll click the link between the two tables and go to my Link Options. Now if you'll notice on the screen, Customer is listed in the left most location, and Credit is on the right. We wanna pull all table records from Customer, so we'll choose a Left Outer Join. We'll click OK. When you click off of link, you'll notice there's an arrow displayed, an arrow that points away from the table that's pulling all records. That way if you're looking at existing reports, you know that if the arrow is touching the other table, that it's pulling all records from the table that doesn't have the arrow.
We'll click OK. We'll refresh our report data. Then we'll scan through the report. Now we see Spokes and Wheels. Spokes and Wheels is a current customer, but they don't have any credit amounts. It's important to remember if you don't adjust the join type, you're working with a default inner join, and that's the most common. This means you're showing data that matches in both tables. Again, if you need to see all from one table, regardless of the information contained in the other table, like our example, Credits, you must adjust the joins.
- Understanding links and join types
- Filtering by null values
- Combining fields
- Creating letters
- Building mailing labels