Express Maintenance SQL

Topics related to Express Maintenance and SQL

Entries feed - Comments feed

Thursday, November 15 2018

SQL Queries and Joins

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. DashBoard.PNG This example has several queries working together

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

((/blog/public/SQL_Joins.png
SQL Joins.png, Nov 2018
from https://www.dofactory.com/sql/join

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 Inner.png 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 Left.png 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 Right.png 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 Outer.png 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