I'm currently learning SQL and came across an obstacle. I have been looking around the web and found no case where the table is being "replicated" as is my case.
The case is based on a classical example where we want to have a result where we get the employee name and the next column with the supervisor name.
Desired result:
|Employee Name | Supervisor Name|
---------------------------------
|Dave | NULL |
|Rick | Dave |
|Andy | Rick |
The current table goes like this (employee):
|emp_id | first_name | last_name| super_id |
--------------------------------------------
|100 | Dave | Star | NULL |
|101 | Rick | Port | 100 |
|101 | Andy | Huds | 101 |
I'm trying to do this with a SELF JOIN in POPSQL (MySQL based) but first I will show how the employee table was created.
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
super_id INT,
);
ALTER TABLE employee
ADD FOREIGN KEY (super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;
This is the code I'm using to make the query:
SELECT m.first_name, s.first_name
FROM employee AS m
INNER JOIN employee AS s
ON m.super_id = s.emp_id;
And below the result I'm getting. Notice how the table has two problems. First, each row seems to be repeated (David appears in two rows). Second, the emp_id is not matched to sup_id (as per the ON m.super_id = s.emp_id).
|emp_id | first_name |
----------------------
|David | David |
|David | David |
|Rick | Rick |
|Rick | Rick |
Could somebody help me? Thanks
if you need to get all records (including Dave), you need to use OUTER JOIN
SELECT m.first_name AS EmployeeName,
s.first_name AS SupervisorName
FROM employee AS m
LEFT JOIN employee AS s
ON m.super_id = s.emp_id;