Table Links – An Explanation

Way back on July 29, 2015 I wrote an article about linking your report and data.  In that article I mentioned:

“Change the Join Type to Left Outer Join. Do this for all remaining links, then click the OK button on the bottom of the window.  (I’ll explain why we are doing this in a future article and link to it here when it is posted.)”

Well, thanks to a recent comment on Facebook’s The Raiser’s Edge Users Group Support Forum I am finally getting around to explaining.

Let’s start with our sample data, which we’ll keep simple and say it looks something like this:

Constituent Table

Phone Table

As you can see we have two tables, the constituent table and the phone table.  Both tables have a field for ConsID, and we will use that to link the two tables.  For our output on the report we want Name, ConsID and Phone fields/columns.  Let’s start with what happens when we link the tables on the ConsID field and use a join type of “Inner Join,”  which is the default in Crystal Reports.

Inner Join

See what’s happening here?  What about Bob?  Unlike in the movie, Bob is gone in this case.  The inner join will only return rows where ConsID links the records in both tables.  In this case Bob does not have a record in the Phone table so he is excluded from the results.

Left Outer Join
When we use a left outer join it returns all rows from the Constituent table (i.e. the left table) whether or not they have a matching row in the Phone table.  In the results above notice that it includes a row for Bob even though he does not have a phone number.

Hopefully this explains the difference between inner join and left outer join.  This is a very basic and simple way to explain it and there’s a lot more that could be covered when it comes to join types.  I’m not saying you must do this for every report you create.  I originally mentioned it because I have made it part of my regular report writing process.  If you don’t change the links to left outer join you might be missing some records (i.e. Bob).  If you are missing records on your report the first thing you should check are your table links.

Oh, and yes Jennifer, you need to change each link individually one by one… and for every new report you create.  There isn’t a way to change all of them at once (that I know of) or to have them default to left outer joins.