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

0 votes

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?

Sep 14 in Database by Kithuzzz
• 11,640 points
22 views

1 answer to this question.

0 votes

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.

answered Sep 16 by narikkadan
• 19,680 points

Related Questions In Database

0 votes
1 answer

What is the SQL query to get the third highest salary of an employee from employee_table

You can try out something like this: SELECT ...READ MORE

answered Sep 27, 2018 in Database by Sahiti
• 6,380 points
7,578 views
0 votes
1 answer

Please name some online websites to compile and run PL/SQL?

For executing Oracle SQL queries and PL/SQL ...READ MORE

answered Feb 11 in Database by Neha
• 8,920 points
149 views
0 votes
1 answer

Which SQL query is used to find Nth highest salary from a salary table

If you want to find nth Salary ...READ MORE

answered Feb 14 in Database by Vaani
• 7,020 points
460 views
0 votes
0 answers
0 votes
1 answer

Which query to use for better performance, join in SQL or using Dataset API?

DataFrames and SparkSQL performed almost about the ...READ MORE

answered Apr 19, 2018 in Apache Spark by kurt_cobain
• 9,390 points
997 views
0 votes
1 answer

How to connect Java program to the MySQL database?

You can connect your Java code with ...READ MORE

answered May 11, 2018 in Java by Parth
• 4,630 points
931 views
0 votes
1 answer
0 votes
1 answer

Automating Oracle script with nolio

Depending upon the details of your script ...READ MORE

answered Jul 17, 2018 in Other DevOps Questions by ajs3033
• 7,280 points
358 views
0 votes
1 answer

SQL Server: Extract Table Meta-Data (description, fields and their data types)

Unfortunately, you have to utilize sysobjects/syscolumns to ...READ MORE

answered Sep 17 in Database by narikkadan
• 19,680 points
11 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
34 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP