CODEWITHSUNDEEP
mysql
kiran
kiran

Reputation: 33

join with same table in mysql?

Im having the following table , each employee have a manager, if a guy is his own manager den the managerid field is null, i want emid name and manager id as result table name is employee

emid name managerid
1    raj  null
2    ram   1
3    ravi   null
4    arvind  3
5    rithu   2

and i want the result as

emid name managername
1    raj  raj
2    ram   raj
3    ravi   ravi
4    arvind  ravi
5    rithu   ram

Upvotes: 3

Views: 237

Answers (4)

manji
manji

Reputation: 47978

SELECT emid, name,
       CASE WHEN s.managerid IS NULL THEN s.name ELSE m.name END managername
  FROM employees s
  LEFT JOIN employees m ON s.managerid = m.managerid

OR

SELECT emid, name, m.name managername
  FROM employees s
  LEFT JOIN employees m ON COALESCE(s.managerid, s.emid) = m.emid

Upvotes: 0

DRapp
DRapp

Reputation: 48149

select
      e1.emid,
      e1.name,
      COALESCE(e2.name, e1.name ) ManagerName
   from 
      employees e1
         left join employees e2
            on e1.managerid = e2.emid

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

SELECT e1.emid, e1.name, COALESCE(e2.name, e1.name) AS managername
    FROM employee e1
        LEFT JOIN employee e2
            ON e1.managerid = e2.emid

Upvotes: 2

Hyperboreus
Hyperboreus

Reputation: 32439

UNTESTED:

select a.name as name, b.name as managername from employee a inner join employee b on a.managerid=b.emid

This will only yield the employees who actually have a manager. But this is how you join a table on itself.

Upvotes: 1

Related Questions