Creating MySQL Joins and Views

Working with MySQL 8 we look at how we can use Joins to show the data we need and how Views to simplify the select statement in MySQL. We have imported the Northwind database to MySQL 8 and we can work with the employees table. This tanles is known as a self-joined table as we have a Foreign Key created on the ReportsTo column that is a join on the EmployeeID column. This just enforces that an employee must report to a manager who is also an employee.

USE northwind;
SELECT Lastname, ReportsTo
FROM employees;

This shows the employees Lastname and the ID of Manager that they report to. We then have to look up the ID ourselves. We have a couple of issues to address. The first is that we have a firstname and lastname but not a fullname. This is quite normal as it would mean repeating data if we stored the full name. To show both the firstname and lastname as the the full name we can use:

USE northwind;
SELECT CONCAT(firstname, ' ', lastname) AS EmployeeName,
ReportsTO 
FROM employees;

This gives us more of what we want, as we see the complete employee name name, but we still need to resolve the ReportsTo column. This is where the MySQL join will be used.

SELECT CONCAT(e.firstname, ' ', e.lastname) AS EmployeeName,
CONCAT(m.firstname, ' ', m.lastname) AS ManagerName
FROM employees as e
JOIN employees as m
ON e.reportsto = m.employeeid;

This shows two columns one with the employee name and the other with their manager’s name. We have to use the table alias e and m in this case as this is the same table and we need to differentiate between the columns that will have the same name.

To simplify the query and add security to the system we will use a view. Ideally, we assign users access to the view and not the table itself. A view is a select statement stored on the server and we now look at creating the MySQL Views and Joins.

CREATE VIEW employee_manager
AS
SELECT CONCAT(e.firstname, ' ', e.lastname) AS EmployeeName,
CONCAT(m.firstname, ' ', m.lastname) AS ManagerName
FROM employees as e
JOIN employees as m
ON e.reportsto = m.employeeid;

We now can have a simple query

SELECT * FROM employee_manager;