CODEWITHSUNDEEP
sqloracle-database
user3525241
user3525241

Reputation: 13

Include rows that contain null values in a particular column with inner join

The goal here is to return the ID, Name, and Manager Name and ID for each Employee. The table does not contain a manager name, only a manager ID for a given employee. The current query works great using an inner join, except that one employee does not have a manager ID (he's the boss), and so none of his information appears. The current query looks like this:

SELECT DISTINCT e.employee_id AS EMPLOYEE_ID, 
                e.FULL_NAME AS EMPLOYEE_NAME, 
                m.manager_ID AS REPORTS_TO, 
                m.FULL_NAME AS MANAGER_NAME
FROM EMPS e
INNER JOIN EMPS m ON e.manager_id = m.employee_id;

How can I include the name and information for this employee despite his lack of a seemingly necessary field? Thanks

Upvotes: 1

Views: 44

Answers (1)

Hogan
Hogan

Reputation: 70523

If you want to include the employee name when there is no manager do a left join:

SELECT DISTINCT e.employee_id AS EMPLOYEE_ID, 
                e.FULL_NAME AS EMPLOYEE_NAME, 
                m.manager_ID AS REPORTS_TO, 
                m.FULL_NAME AS MANAGER_NAME
FROM EMPS e
LEFT JOIN EMPS m ON e.manager_id = m.employee_id;

Upvotes: 1

Related Questions