Case when field is in another field

1

Hi all,

I need to construct a query which will tell me if the primary key field (empl_no) appears in another field, supervisor_id, in the same table. Something like this:

select empl_no,
case when
----if empl_no is in supervisor_id
then 'yes'
else 'no'
end as 'Manager'
from employee

Thanks so much for your help. I truly appreciate it.

2 
select emp.empl_no, 
    case when exists(select 1 from dbo.table_name sup where sup.supervisor_id = emp.empl_no)
         then 'yes' else 'no' end as 'Manager'
from dbo.table_name emp
1 Like
3

Thank you so much Scott.

4

You're welcome very much!

5

One question though. How would I fit this into an already larger query regarding the same table?

SELECT a.empl_no as 'EXTERNAL_HR_ID',
a.f_name as 'FIRST_NAME',
a.l_name as 'LAST_NAME',
CASE WHEN exists(select 1 from dbo.table_name awhere a.supervisor_id = b.empl_no)
then 'yes' else 'no' end as 'Manager*
from dbo.table_name b,
a.birthday as 'Birthday',
a.code as 'Code ID'
from dbo.table_name a

6

Yep, just like that (well, with a space between then table alias "a" and "where a....", but I figure that's just a typo). That whole CASE expression is just another column, it shouldn't affect other columns in the query.

7

Something is not right because I run when a simplied version for testing purposes, I get 'incorrect syntax near the keyword 'from':

select empl_no,
f_name,
CASE WHEN exists (select 1 from employee a where a.empl_no = b.supervisor_id) then 'yes' else ' no'
end as 'Manager'
from employee b,
code,
hiredate
from employee

8

Well I think I just figured it out. :slight_smile:

9

Another option:

DECLARE @emp table(empid int, supid int);

INSERT @emp ( empid, supid )
VALUES ( 1, 1 ), ( 2, 1 ), ( 3, 1 );

SELECT
   e.empid
 , e.supid
 , CASE WHEN e.empid=e.supid THEN 'Yes' ELSE 'No' END Maneger
FROM
   @emp e;

image