CODEWITHSUNDEEP
sqlsql-serverpostgresqlself-join
Kavi
Kavi

Reputation: 140

Efficient way to handle below query? in SQLServer or PostgreSql (Self join)

I have an employee table as below

select * from employee

I need to select employee_id, manager_id, result. result should have true or false depends on below conditions.

  1. If the employee is manager for someone then true
  2. If the employee has a manager then true

I came up with a query, but I need to know if there are any other better way to do it. This is my query

with manager  as
(
select distinct manager_id from employee where manager_id is not null
)
select 
e.employee_id, e.manager_id , m.manager_id, 
case when e.manager_id is not null then true
     when m.manager_id is not null then true 
     else false
end as Result
from employee e left join manager m  on e.employee_id = m.manager_id

and the result should come like this

enter image description here

Upvotes: 2

Views: 68

Answers (1)

gotqn
gotqn

Reputation: 43646

Maybe something like this:

select
  e.employee_id,
  e.manager_id,
  (
    e.manager_id is not null
    or
    exists(select 1 from employee m where m.manager_id=e.employee_id)
  ) as result
from employee e;

Upvotes: 2

Related Questions