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.

Is department_id equal to employee.id? – Maciej Mar 06 '13 at 16:06 — Maciej, Mar 06 '13 at 16:06

John Woo · Accepted Answer · 2013-03-06T15:57:33.447

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)             ║
╚════╩══════╩════════════════════╝

Very cool, I didn't know I could filter within the join. This will also help in other areas. — Rich, Mar 06 '13 at 16:36

score 1 · Answer 2 · answered Mar 06 '13 at 15:48

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.

score 1 · Answer 3 · answered Mar 06 '13 at 15:50

1

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

WHERE training.Id = 1

if that's equivalent

answered Mar 06 '13 at 15:50

Melanie

3,021
6
38
56

score 0 · Answer 4 · edited Mar 06 '13 at 15:55

0

What you are looking for is called an Outer Join. In this case you will need a left outer join:

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

edited Mar 06 '13 at 15:55

Xavi López

27,550
11
97
161

answered Mar 06 '13 at 15:49

Jeff70227

11
4

score 0 · Answer 5 · answered Mar 06 '13 at 15:54

0

Select e.id, e.name, t.class from employee e left outer join training t on e.id = t.department_id where t.class like '%Security%' order by e.id

answered Mar 06 '13 at 15:54

Usman YousafZai

1,088
4
18
44

SQL query one to many relationship

5 Answers5

Linked