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

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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;