George's Notes

To get a good report out of Express Maintenance it starts with a good SQL query and how you join the tables to get the data you are looking for. This example has several queries working together

To do that you first need to know what tables have the data you need.

JoinsFrom Code Project Website https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins https://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins Inner JOIN This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key

Example: Table A is units and Table B is WorkOrders. The output would be all of the WorkOrders where the Units from Table A matched.

Left JOIN This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key

Right JOIN This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key

Outer JOIN This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key