CODEWITHSUNDEEP
sql
Vin rich
Vin rich

Reputation: 29

find all employees' names who has a manager that lives in the same city as them

I am trying to figure out how to "find all employees' name who has a manager that lives in the same city as them." For this problem, we have two tables. We need to make a query.

"employee" The employee table that we can refer to has both normal employees and managers

employeeid name projectid city
1 jeff 1 new york
2 larry 1 new york
3 Linda 2 detroit
4 tom 2 LA

"Managertable" Our manager table which we can refer to with mangerid = employeeid

projectid mangerid
1 2
2 3

Right now I have found a way to get just the employees and filter out the managers, but now I am trying to figure out the next step to get to the comparison of managers and employees. Would this just be another subquery?

SELECT name  
FROM employee e
WHERE employeeid not in( 
SELECT mangerid
FROM Managertable pm
INNER JOIN employee e
ON pm.mangerid= e.employeeid);

Expected result :

employee name
jeff

Upvotes: 1

Views: 668

Answers (5)

mga44
mga44

Reputation: 11

I think the easient way to achieve this would be like this:

SELECT
  e.*
FROM employee e
  inner join Managertable mt on e.projectid = mt.projectid
  inner join employee manager on mt.mangerid = manager.employeeid
WHERE
  e.city = manager.city
  and e.employeeid <> manager.employeeid;

Upvotes: 1

lemon
lemon

Reputation: 15492

You just need two joins:

  • one between "managers" and "employees" to gather managers information
  • one between "managers" and "employees" to gather employees information with respect to the manager's projectid and city.
SELECT employees.name
FROM       managers
INNER JOIN employees managers_info
        ON managers.mangerid = managers_info.employeeid
INNER JOIN employees 
        ON managers.projectid = employees.projectid
       AND managers_info.employeeid <> employees.employeeid
       AND managers_info.city = employees.city

Upvotes: 0

Isolated
Isolated

Reputation: 6454

I don't see how projectid is relevant in your question because you didn't mention that as a requirement or restriction. Here's a method using a CTE to get the managers and their cities, then join to it to find employees who live in the same city as a manager.

with all_managers as (
 select distinct m.managerid, e.city
 from manager m 
 join employee e 
   on m.managerid = e.employeeid 
   )
select e.name 
from employee e 
join all_managers a 
  on e.city = a.city
 and e.employeeid <> a.managerid;
name
jeff

But it you want us to assume that an employee reports to only that manager as listed in the projectid, then here's a modification to ensure that is met:

 with all_managers as (
  select distinct m.managerid, e.city, e.projectid
  from manager m 
  join employee e 
    on m.managerid = e.employeeid 
   )
select e.name 
from employee e 
join all_managers a 
  on e.city = a.city
 and e.projectid = a.projectid
 and e.employeeid <> a.managerid;

View on DB Fiddle

Upvotes: 0

Neon Tetra
Neon Tetra

Reputation: 159

Based off the table structure you're showing, something like this might work

First find the employee ids of employees who have managers in the same city, then join it back on employee to retrieve all data from the table

;WITH same_city AS (
      SELECT DISTINCT e.employeeid
        FROM employee AS e
  INNER JOIN managertable AS mt ON  e.projectid = mt.projectid
  INNER JOIN employee     AS m  ON mt.managerid =  e.employeeid
       WHERE e.city = m.city
)
    SELECT e.* 
      FROM employee 
INNER JOIN same_city AS sc ON e.employeeid = sc.employeeid

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

One approach is a correlated subquery in which we look up the employee's manager's city.

select e.name
from employee e
where city =
(
  select m.city
  from managertable mt
  join employee m on m.employeeid = mt.managerid
  where mt.projectid = e.projectid
  and m.employeeid <> e.employeeid
);

The same thing can be written with an EXISTS clause, if you like that better.

Upvotes: 0

Related Questions