Background
The CMMS employs a relational database so many tables are referenced in others. You will notice this in dropdown menus in various pages in the CMMS. For example, when assigning an asset category to an asset, you are simply creating a reference to the asset category table on the asset record.
If certain fields you need are not available in the main table, you can add supplementary tables to the report using a join. If you open out the asset table in the report writer, you can see quickly which fields are simple text fields, and which fields reference other tables.
You can quickly identify which fields you need to reference from another table where you see the blue Join hyperlink:
To add the data from another table in the database, you must join to that other table in some way. For example, if I want to add the asset category details in a column in my report, I need to join the Category ID in the asset table to the Asset Category table:
When you click the blue Join hyperlink, you get the following pop-up window which outlines the join condition:
You are faced with 3 options - Inner join, Left outer join or Right outer. This join can determine which data you see in your report so extra care must be taken when making your decision.
What are the 3 Types of Joins?
1. Inner Join
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. If you join with an inner join, the data will only show up in the report if it exists in the 2 tables.
Example 1 - If you INNER JOIN assets to the asset category table, and one particular asset does not have a category, it will not show up in the report. If you build a report that shows a list of assets, the assets with no asset category assigned will not show up on the report.
Example 2 – If you INNER JOIN the work order table to the work order part table, only work orders with parts in them will show up in the report. Your report will only show work orders with parts used. Any work orders without parts will not show up.
An inner join may come in handy when you want to eliminate some information from your report. For example, you may wish to see a list of assets with the corresponding scheduled maintenance for that asset. Inner joining the asset table to the scheduled maintenance table will ensure the report only shows assets that have a scheduled maintenance assigned to them.
If you wish to see a full list of assets, regardless of whether they have scheduled maintenance or not, then a left outer join should be used.
2. Left Outer Join
The LEFT OUTER JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
Example 1 - If you use LEFT OUTER JOIN to join assets to the asset category table, and one particular asset does not have a category, it will still show up in the report.
Example 2 – If you LEFT OUTER JOIN the work order table to the work order part table, all work orders, regardless if they have parts or not, will show up in the report.
Note:- Unless a record must have a certain condition to exist, then the left outer join will most likely be used.
3. Right Outer Join
The RIGHT OUTER JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
Example 1 – If you RIGHT OUTER JOIN the asset table to the work order table, all work orders regardless if they have an assigned asset or not will show up in the report.
When do I use each type of Join?
-
Inner Join: All of the records from both tables, that have a value in the field that was joined, will be included.
-
Left Outer Join: All of the records in the first (left-hand) table are included even if there are no matches in the second (right-hand) table. This is the default join type and the most common when building CMMS reports.
-
Right Outer Join: All of the records in the second (right-hand) table are included even if there are no matches in the first (left-hand) table.
Any join will show beneath the main table. Unlike the master table, joins can be added at any time and can also be removed from the report if you either chose the wrong table or no longer need its information. You can add multiple joined tables. Tables can be joined directly to the main table or joined to another table, which is joined to the main table. Click the + for the table to which you want to join another.