Topic Description
Find the last_name and first_name for all employees and the corresponding department number dept_no, including showing employees who are not assigned specific departments
CREATE TABLE ' dept_emp ' (
' emp_no ' int (one) not null,
' Dept_no ' char (4) isn't null,
' from_date ' date not null,
' to_date ' date not NULL,
PRIMARY KEY (' emp_no ', ' dept_no '));
CREATE TABLE ' Employees ' (
' emp_no ' int (one) not null,
' birth_date ' date not null,
' first_name ' varchar no T null,
' last_name ' varchar NOT NULL,
' gender ' char (1) is not NULL,
' hire_date ' date is not NULL,
PRIMARY KEY (' emp_no '));
Output Description:
last_name |
first_name |
Dept_no |
Facello
|
Georgi |
d001 |
Omitted |
Omitted |
Omitted |
Sluis |
Mary |
Null (null here in SQLite, MySQL is null) |
Solution
Focus on the relationship between the two tables, because the department number can be empty, so here is the employee table as the main table, the Department table is associated table, you can use the left or right association.
Select Emp.last_name,emp.first_name,dep.dept_no from
employees EMP left
join dept_emp dep on
emp.emp_no = Dep.emp_no