-2

I am faced with this problem and have no idea how to do it. I have the following question. Show the names of all the departments in the company together with the names of their managers. Your query must show all the departments in the company, even if it has no manager.

My problem is that when I ran a query to give me department names only, I got 27 results. However for the managers query there are just 18 results.

I have no idea how to do this. The Queries I used to get this are, for the department names:

--select department_name from departments;
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll

for the second part:

select first_name, last_name, department_name from employees e inner join departments d on
d.department_id=e.department_id where (employee_id in (select manager_id from employees));

first_name  last_name   department_name
Steven  King    Executive
Neena   Kochhar Executive
Lex De Haan Executive
Alexander   Hunold  IT
Nancy   Greenberg   Finance
Den Raphaely    Purchasing
Matthew Weiss   Shipping
Adam    Fripp   Shipping
Payam   Kaufling    Shipping
Shanta  Vollman Shipping
Kevin   Mourgos Shipping
John    Russell Sales
Karen   Partners    Sales
Alberto Errazuriz   Sales
Gerald  Cambrault   Sales
Eleni   Zlotkey Sales
Michael Hartstein   Marketing
Shelley Higgins Accounting

the second part returns the departments only with managers.

Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
Paloma D'Silva
  • 5
  • 1
  • 1
    Assuming you want to learn, and not to have us do your homework, look at http://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join – Florian Heer Oct 11 '16 at 00:01
  • never mind guys it was an outer join, my prof cleared this up!! got it working – Paloma D'Silva Oct 13 '16 at 02:38

2 Answers2

0
select e.first_name, e.last_name, d.department_name
from employee e right join department d 
    on e.department_id = d.department_id
where e.employee_id in (select manager_id from employee)
    or e.manager_id is null;
Chen
  • 1
  • 2
0

For those who are interested:

select d.department_name as 'Department Name', e.first_name as 'Manager First Name', e.last_name as 'Manager Last Name' from departments d left outer join employees e on d.department_id = e.department_id and (employee_id in (select manager_id from employees)) order by department_name;

Got the answer from my prof

Paloma D'Silva
  • 5
  • 1