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.
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
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
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.
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