CODEWITHSUNDEEP
sqljoin
Rich
Rich

Reputation: 193

SQL query one to many relationship

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.

Upvotes: 19

Views: 141474

Answers (5)

John Woo
John Woo

Reputation: 263683

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

RESULT

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

Upvotes: 24

Jeff70227
Jeff70227

Reputation: 11

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

Upvotes: 0

Usman YousafZai
Usman YousafZai

Reputation: 1120

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

Upvotes: 0

Melanie
Melanie

Reputation: 3111

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

WHERE training.Id = 1

if that's equivalent

Upvotes: 1

Gojira
Gojira

Reputation: 3041

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.

Upvotes: 1

Related Questions