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.

Comments

mysql for emp and dept table query