Difference between revisions of "Using an Outer Join"
| Line 36: | Line 36: | ||
{{PrevNextMid}} [[Building Sub-Reports]] | {{PrevNextMid}} [[Building Sub-Reports]] | ||
{{PrevNextEnd}} | {{PrevNextEnd}} | ||
| + | |||
| + | ==See Also== | ||
| + | * [[Reports_Based_on_Multiple_Tables_-_Overview#Inner_and_Outer_Joins|Definition of Inner and Outer Joins]] | ||
[[Category:Reports]] | [[Category:Reports]] | ||
Revision as of 08:29, 1 May 2013
Click here to watch a video on SmartSimple's integrated reporting subsystem.
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.
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.
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:
2. Build, save, and preview the report.
You can hide the two columns that are not displaying any date by clicking the Show check box for these two columns.
