SQL query - Dept and Employee table some departments may not have managers

Question

There are two tables:

Employee 
---------
emp_id
emp_name 
emp_contact 
salary 
mgr_emp_Id
Dept_No 

Dept 
-----
Dept_No
Dept_name
Dept_Location

Create a SQL statement that lists all department names, location information, and, if applicable, the manager's name for each department that has one. Keep in mind that some divisions still lack a manager. Is what follows true?

SELECT Dept_name, Dept_Location, emp_name AS Mgr_name 
FROM Dept 
LEFT JOIN Employee ON (Dept.Dept_No = Employee.Dept_No AND mgr_emp_id = emp_id)

Can this be achieved without join too? If yes, how?

Answer 1

Yes, even though I like using JOINs, you can accomplish it without one. You'll need to change your query. This is how...

Example:

http://sqlfiddle.com/#!2/596b45/4 (MySQL 5.5)

http://sqlfiddle.com/#!3/bbad4/1 (SQL Server 2008)

http://sqlfiddle.com/#!12/bbad4/1 (PostgreSQL 9.2)

Assumption

1) In the employee table: manager id + department id will be unique

2) In the employee table: if employee Clark is ID 5 and has a manager with ID 1, there will a record in the table with ID 1

3) MySQL 5.5 is being used

Structure

create table dept
(
  dept_no int not null,
  dept_name varchar(100) not null,
  dept_location varchar(100) not null,
  primary key (dept_no)
);

create table employee 
(
  emp_id int not null,
  emp_name varchar(100) not null,
  mgr_emp_id int,
  dept_no int not null,
  primary key (emp_id),
  key employee_mgr_emp_id (mgr_emp_id),
  foreign key fk_employee_dept_dept_no (dept_no) references dept (dept_no) on delete no action on update no action
);

insert into dept values 
(1, 'Dept-1', 'Chicago'), 
(2, 'Dept-2', 'London');

insert into employee values 
(1, 'Clark Mgr', null, 1),
(2, 'Cameron Emp', 1, 1),
(3, 'Charlie Emp', 1, 1),
(4, 'Layton Emp', null, 2),
(5, 'Linda Emp', null, 2);

MySQL 5.5 query

Without JOIN

select 
  list.*,
  emp_id,
  emp_name
from employee, 
(
  select
    distinct 
    dept.dept_no, 
    dept.dept_name, 
    dept.dept_location, 
    employee.mgr_emp_id
  from dept, employee
  where 
    dept.dept_no = employee.dept_no
    and employee.mgr_emp_id is not null
) list
where
  employee.emp_id = list.mgr_emp_id;

With JOIN (although not completely identical to the above. I prefer using JOINs)

select 
  list.*,
  emp_id,
  emp_name
from employee
inner join 
(
  select
    distinct 
    dept.dept_no, 
    dept.dept_name, 
    dept.dept_location, 
    employee.mgr_emp_id
  from dept
  left join employee on dept.dept_no = employee.dept_no
  where 
    employee.mgr_emp_id is not null
) list
on employee.emp_id = list.mgr_emp_id;

How does it work

The first thing we need is a list of all the departments in the employee table with manager IDs that are not null. We use the following query to achieve that. Due to the existence of 2 records in the employee database with a valid manager ID for Chicago's department, this query returns 2 records for Chicago.

Without JOIN

select
  dept.dept_no,
  dept.dept_name,
  dept.dept_location,
  employee.mgr_emp_id
from dept, employee
where
    dept.dept_no = employee.dept_no
    and employee.mgr_emp_id is not null;

With JOIN

select
  dept.dept_no,
  dept.dept_name,
  dept.dept_location,
  employee.mgr_emp_id
from dept
left join employee on dept.dept_no = employee.dept_no
where
    employee.mgr_emp_id is not null;

To get only one record, we will use distinct keyword:

  select
    distinct 
    dept.dept_no, 
    ...

Great, so now we know who the manager is for each department no. Let's find this person's name. To do that, we put our query in a subquery (which I nicknamed/aliased as list) and then combine it with employee table to get the desired result.

To know more about SQL, It's recommended to join SQL Course today.