SQL query one to many relationship

Question

I have a table for Employees and another table with Training. The training table contains various training classes that the employees have completed. We have mandatory security awareness training, so every employee must complete this training class. I’m having trouble running a query that will return ALL employees' either listed completing the training or not.

Example Employee table

╔════╦══════╗
║ ID ║ NAME ║
╠════╬══════╣
║  1 ║ Bob  ║
║  2 ║ Tom  ║
║  3 ║ John ║
╚════╩══════╩

Example Training table

╔════╦══════════════╦════════════════════╗
║ ID ║ DEPARTMENT_ID║       CLASS        ║
╠════╬══════════════╬════════════════════╣
║  1 ║           1  ║ Security Awareness ║
║  2 ║           1  ║ Workplace Safety   ║
║  3 ║           2  ║ Security Awareness ║
╚════╩══════════════╩════════════════════╝

Target result

╔════╦══════╦════════════════════╗
║ ID ║ NAME ║       CLASS        ║
╠════╬══════╬════════════════════╣
║  1 ║ Bob  ║ Security Awareness ║
║  2 ║ Tom  ║ Security Awareness ║
║  3 ║ John ║ (null)             ║
╚════╩══════╩════════════════════╝

The query that I am using is

SELECT employee.id, employee.name, training.class
FROM employee
JOIN training ON employee.id = training.department_id
WHERE training.class LIKE '%SECURITY%'
ORDER BY employee_id

The employee missing the "Security Awareness" class just don't appear, and falls through the cracks.

Solution 1

use LEFT JOIN and move the filtering condition during the joining of the table (specifically in the ON clause)

Another concern is use single quotes: ' ' not ‘ ’

SELECT  employee.id, 
        employee.name, training.class
FROM    employee   
        LEFT JOIN training 
            ON employee.id = training.department_id AND
                training.class LIKE '%SECURITY%'
ORDER   BY employee.id

• SQLFiddle Demo

RESULT

╔════╦══════╦════════════════════╗
║ ID ║ NAME ║       CLASS        ║
╠════╬══════╬════════════════════╣
║  1 ║ Bob  ║ Security Awareness ║
║  2 ║ Tom  ║ Security Awareness ║
║  3 ║ John ║ (null)             ║
╚════╩══════╩════════════════════╝

Solution 2

Instead of JOIN use LEFT OUTER JOIN. I'd also change your WHERE clause to

WHERE training.Id = 1

if that's equivalent

Solution 3

You are performing an inner join, what you want is a left outer join. The difference is: an inner join will only results where there is a match in the joined table. A left outer join will return all results from the primary table, whether there are results in the joined table or not.

Comments

[Maciej]

Is department_id equal to employee.id?

[Rich]

Very cool, I didn't know I could filter within the join. This will also help in other areas.