CODEWITHSUNDEEP
sqlsql-servert-sql
KWallace
KWallace

Reputation: 632

How to mark employees which are also manager

I need to determine whether an employee is any other employee's manager.

Given this table:

Employee    Employee's Manager
----------  ------------------
Bob         CN=Lisa              
Amanda      CN=Lisa              
James       CN=Art               
Frank       CN=Amanda            
Amy         CN=Art

I need this:

Employee    Employee's Manager  Employee IS Manager
----------  ------------------  -------------------
Bob         CN=Lisa              N
Amanda <--  CN=Lisa              Y <--
James       CN=Art               N
Frank       CN=Amanda <--        N
Amy         CN=Art               N

Because Amanda appears in the "Employee's Manager" column in another employee's row, I need to derive this, adding the additional "Employee IS Manager" field.

I've gotten as far as this (wrong!) subquery for the additional "IS Manager" field, but I do not know how to add it as a column in a subquery:

select 
    a.* , 
    (select 'Y' as IsManager 
        where exists (select * from Employees b where b.Manager like '%' + @x+ '%' )
    )
 from Employees a

But I do not know how to make @x refer to Amanda in the Employee column in the other row.

EDIT: I should note that I am not necessarily looking for a "subquery" solution. A JOIN solution, or any other kind of solution is fine for my purposes. Thanks.

Upvotes: 0

Views: 138

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You are close but you need a case expression:

select e.* , 
       (case when exists (select 1
                          from Employees m
                          where m.Manager like '%=' + e.employee_manager
                         )
             then 'Y' else 'N' end
        ) as isManager
from Employees e;

Note that I tweaked the logic for matching so "Anne" and Roseanne" do not get confused. If the manager always starts with 'CN=', then use like 'CN=' + instead.

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can also use outer apply to get your desired result. Here through outer apply we are getting 'Y' when an employee is also a manager other wise it's returning null. Coalesce() is used to convert null to 'N'.

Schema and insert statements:

 create table Employees (Employee    varchar(50),employee_Manager varchar(50));
 insert into Employees  values('Bob',         'CN=Lisa');              
 insert into Employees  values('Amanda',      'CN=Lisa');              
 insert into Employees  values('James',       'CN=Art' );              
 insert into Employees  values('Frank',       'CN=Amanda');            
 insert into Employees  values('Amy',         'CN=Art'   );

Query:

 select 
    a.*,coalesce(isManager,'N')[Employee IS Manager]
  from Employees a outer apply(select 'Y' from Employees b where b.employee_manager='CN='+a.Employee)manager (isManager)

Output:

Employee employee_Manager Employee IS Manager
Bob CN=Lisa N
Amanda CN=Lisa Y
James CN=Art N
Frank CN=Amanda N
Amy CN=Art N

db<fiddle here

Upvotes: 1

Related Questions