Difference between revisions of "Using an Outer Join"

From SmartWiki
Jump to: navigation, search
(→‎Locating Null Records)
 
(13 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
{{SeqReportPageHeader}}
+
{{Banner-UnderConstruction}}
  
 +
 +
 +
==Overview==
 +
This article will explain how to use the '''Outer Join''' option for when you are creating a [[Reports|report]] with multiple [[Report Recordsets|recordsets]]. This feature is applicable only for reports with '''[[Classic Report Builder - Column Settings|Builder Type - Classic]]''' (as opposed to our more recent and recommended option, the '''[[Report Builder (Neo)|Neo Report Builder]].'''
 +
 +
In a nutshell, the '''Outer Join''' option modifies your report-building so that your report query will display columns from multiple tables, even without matching records. The records that do not match with both tables will simply show up blank.
 +
 +
===Inner vs Outer Join===
 +
There are two types of '''Joins '''that can be used in the '''Classic Report Builder '''to connect recordsets in a report: 
 +
 +
{| class="wikitable"
 +
|-
 +
!|Inner Join
 +
||
 +
The '''inner join''' displays the intersection between the two selected tables.
 +
 +
This is the most common type of join used, and is the default when you [[Adding a Table to a Report|add tables to a report]].
 +
 +
For example: When you join the organization and contact table with an inner join, the report will find the matching record(s) from the contact table related to each organization. If no match is found, the record from the company will not be included in the report results. If multiple results are found in the contact table, the company information will be repeated.
 +
 +
|-
 +
!|Outer Join
 +
||
 +
The '''outer join''' is very different from an inner join. Instead of limiting results to those from both tables, it limits results to those in the "left" organization table.
 +
 +
For example: Assume that you have used the outer join to connect the organization and contact table. If there are no records found in the organization table that match with contacts, a row in the report will still be returned for that organization, but with NULL values for each column taken from contact fields.
 +
 +
Only use the outer join if you would still like all results (regardless of matching records with other table) to show up.
 +
 +
|}
 +
===Enabling Outer Join===
 +
The '''Outer Join '''connecting functionality (and to connect multiple tables to a report in general) is accessed from the '''Edit Report '''page in the second tab labelled '''Recordsets.'''
 +
 +
1. Click on the 9-square menu icon on the top right of your page.
 +
 +
:: {{Icon-Menu}} 
 +
2. Under the heading '''Tools, '''select '''Reports.'''
 +
 +
3. Click on the '''pencil icon '''next to the report that you would like to edit. 
 +
 +
4. Click into the second tab labelled '''Recordsets.'''
 +
 +
:: [[File:Recordsets inner outer join.png|thumb|none|600px|border|Outer Join option available for fields found in the Recordsets tab]]
 +
===Example of Outer Join for Reports===
 
In this article you will modify the query to display companies where there are no contacts.
 
In this article you will modify the query to display companies where there are no contacts.
  
Line 6: Line 50:
  
 
[[Image:Reps96.png]]
 
[[Image:Reps96.png]]
−
+
 
 
2. Open the '''Query Builder'''.
 
2. Open the '''Query Builder'''.
  
Line 14: Line 58:
  
 
[[Image:Reps95.png]]
 
[[Image:Reps95.png]]
−
+
 
 
The company records with no contacts are displayed at the top of the list.
 
The company records with no contacts are displayed at the top of the list.
  
 
=Locating Null Records=
 
=Locating Null Records=
−
 
 
You can also use this technique to restrict the results to records where there are '''NO''' matching records in the joined table.
 
You can also use this technique to restrict the results to records where there are '''NO''' matching records in the joined table.
  
Line 26: Line 69:
  
 
[[Image:Reps94.png]]
 
[[Image:Reps94.png]]
−
+
 
 
2. '''Build''', '''save''', and '''preview''' the report.
 
2. '''Build''', '''save''', and '''preview''' the report.
  
 
[[Image:Reps93.png]]
 
[[Image:Reps93.png]]
−
+
 
 
You can hide the two columns that are not displaying any date by clicking the '''Show''' check box for these two columns.
 
You can hide the two columns that are not displaying any date by clicking the '''Show''' check box for these two columns.
−
+
 
−
{{PrevNextStart}} [[Reports Based on Multiple Tables - Overview]]
+
{{PrevNextStart}} [[Reports Based on Multiple Tables - Overview]]
−
{{PrevNextMid}} [[Building Sub-Reports]] {{PrevNextEnd}}
+
{{PrevNextMid}} [[Building Sub-Reports]] {{PrevNextEnd}}
−
|}
+
 
  
  

Latest revision as of 15:51, 10 July 2019


Construction warning.png Please note that this page is currently under construction. There is more information to come.


Overview

This article will explain how to use the Outer Join option for when you are creating a report with multiple recordsets. This feature is applicable only for reports with Builder Type - Classic (as opposed to our more recent and recommended option, the Neo Report Builder.

In a nutshell, the Outer Join option modifies your report-building so that your report query will display columns from multiple tables, even without matching records. The records that do not match with both tables will simply show up blank.

Inner vs Outer Join

There are two types of Joins that can be used in the Classic Report Builder to connect recordsets in a report: 

Inner Join

The inner join displays the intersection between the two selected tables.

This is the most common type of join used, and is the default when you add tables to a report.

For example: When you join the organization and contact table with an inner join, the report will find the matching record(s) from the contact table related to each organization. If no match is found, the record from the company will not be included in the report results. If multiple results are found in the contact table, the company information will be repeated.

Outer Join

The outer join is very different from an inner join. Instead of limiting results to those from both tables, it limits results to those in the "left" organization table.

For example: Assume that you have used the outer join to connect the organization and contact table. If there are no records found in the organization table that match with contacts, a row in the report will still be returned for that organization, but with NULL values for each column taken from contact fields.

Only use the outer join if you would still like all results (regardless of matching records with other table) to show up.

Enabling Outer Join

The Outer Join connecting functionality (and to connect multiple tables to a report in general) is accessed from the Edit Report page in the second tab labelled Recordsets.

1. Click on the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Tools, select Reports.

3. Click on the pencil icon next to the report that you would like to edit. 

4. Click into the second tab labelled Recordsets.

Outer Join option available for fields found in the Recordsets tab

Example of Outer Join for Reports

In this article you will modify the query to display companies where there are no contacts.

1. Change the join type on the Contact table to Outer Join.

Reps96.png

2. Open the Query Builder.

3. Change the Sort order for the Last name field to display the records in descending sequence.

4. Build, save, and preview the report.

Reps95.png

The company records with no contacts are displayed at the top of the list.

Locating Null Records

You can also use this technique to restrict the results to records where there are NO matching records in the joined table.

By applying the Is Null expression to the Last name column, you can display a list of only the companies with no contacts.

1. Modify the report as shown below:

Reps94.png

2. Build, save, and preview the report.

Reps93.png

You can hide the two columns that are not displaying any date by clicking the Show check box for these two columns.




Previous.png Reports Based on Multiple Tables - Overview Building Sub-Reports Next.png


See Also
Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Using_an_Outer_Join&oldid=36625’