Use outer Join for Table Linking

There are times when you will pulling information from two tables.  Keep in mind that if you choose a field to list on your report that is specific to only one of the tables, the results will be limited to those records that have that particular field populated.

For example, you are trying to pull records from Individuals and the Account Number field from the Membership database.  If you place both tables in one Data Pipeline, the report will only include the individuals who are designated as the member contact in the Trade Membership table who have an Account Number populated.  This is because of the automatic linking that takes place.   This is referred to as an Inner Join.

 In order to pull ALL INDIVIDUALS into the report, you will need to have two data pipelines.  Create one with just the Individual Table including the fields you wish to list in the report along with the ID number.  Then using the Query Wizard, create another data pipeline using the Trade/Professional Membership table and pull in the fields for Account Number and Member ID. Finish.

Now when you look at the Data Tab you should see both Data Pipelines with the fields listed. The next step is to link them manually.  With your mouse, left click on the Member ID in the Trade Member Data and drag to the ID No field in the Individual Data pipeline.  Let go of the mouse button and the link should show a "1" next to ID No and "oo" next to Member ID.  This is the 'one to many' relationship.  

Now double-click on the line of the link you just created and it pops up with a Link screen. At the bottom left of the screen, you will choose option 2 that states - Include all records from Individual, regardless of whether any matching Trade Membership records can be found.  This is usually referred to as an Outer Join.

On the Design tab, you can now use the Report Wizard to define the layout of the fields from both tables.  This report will now display all your INDV records whether or not they are the Member Contact in the Trade Membership table or not.  Only those individuals designated as the Contact Member  in the Trade Membership table will include the Account Number with their record.