Menu
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.
867
Example of one-to-many relation in SQL ServerCreate two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.
In a relational database, a one-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A. It is important to note that a one-to-many relationship is not a property of the data, but rather of the relationship itself.
In relational databases, a one-to-many relationship occurs when a parent record in one table can potentially reference several child records in another table.
The table on the "one" side of the "one-to-many" relationship should have a primary key column. The other table should have a foreign-key defined pointing to the primary key on the first table. To return results from both tables you'd add an INNER JOIN clause to join both tables.
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) ║
╚════╩══════╩════════════════════╝
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
