DARTS : Join Types

When writing queries, it is fairly simple to retrieve information from one table. In many cases, you want to retrieve data from more than one table or specify criteria in your query from a second table. In these cases, you need to link at least two tables in one query. Working with multiple tables is almost as easy as working with one table.

A join enables you to retrieve data from two or more records or to specify criteria from more than one record. When you perform a join, you link records based on their common fields.

Join types in PS Query
Standard Join
Left outer join
Basic Join Tip
Step by Step Instructions

Join types in PS Query

These two types of joins are used in PS Query : 1) Standard Join and 2) Left Outer Join
join diagram

Standard Join

Standard join returns data only when there is a matching record in both records.

For example, the record A has 10 rows, but the record B only has 7rows that matched by common field (ID in this example) , the query after join returns 7 rows.

Left outer join

Left outer join returns all of the rows in the first record (Record A). If there are no match in record B, additional data will be returned BLANK.

For example, the record A has 10 rows. After doing a left outer join, the query still shows 10 rows.

Back to Top

Basic Join Tip

Join only one record at a time and run the query to review the data before joining the additional records.
Checking the data with small sample will help.
Do not mix join types. If you use Left Outer Join for the first join, the rest of the joins will be Left Outer Joins.
Use UM_ (University of Maine System)'s reporting tables where tables were already joined and applied business logics
If the query result is not what you are expecting, check "Belongs to" column (Left Outer Join)

Click Edit button for the criteria and you can change under the drop box for "This criteria belongs to".