Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table employee

+--------+----------+------------+
| emp_id | emp_name | manager_id |
+--------+----------+------------+
| 1      | abc      |    2       |
| 2      | pqr      |    3       |
| 3      | xyz      |    n       |
| ....   | .....    |    ....    |
+--------+----------+------------+   

I need result in following format:

+----------+--------------+
| Emp_name | Manager_name |
+----------|--------------+
| abc      |  pqr         |
| pqr      |  xyz         |
| ....     |  ....        |
+----------+--------------+

How to get result in this format? Plz help..

share|improve this question
2  
Please tell us what concrete database system this is for - many things are vendor-specific. Are you using MySQL? Postgres? SQL Server? Oracle? IBM DB2? Something else entirely? Please update your tags to show what database system (and which version of it!) you're using - thanks! –  marc_s Nov 24 '13 at 10:24
    
@marc_s: Thanks. I am using MySQL. Updated the tag. –  Abhishek Nov 24 '13 at 15:29

2 Answers 2

up vote 2 down vote accepted

A join to the same table is going to have the same syntax as a join to a different table.

If there will always be a value in manager_id, then you can use an INNER JOIN.

If the manager_id might be NULL or have an invalid value (someone's application decided to use a sentinel value like -1 because "nulls are hard")) then you're looking at a LEFT OUTER JOIN.

Usually the quick way to answer that question is to find the CEO/President/Top Dog and look at how they were stored.

SELECT
    E1.emp_name AS Emp_name
,   COALESCE(E2.emp_name, 'Unknown') AS Manager_name
FROM
    EMPLOYEE AS E1
    -- Change this join type as required by your data
    LEFT OUTER JOIN
        EMPLOYEE AS E2
        ON E2.emp_id = E1.manager_id

The COALESCE function is going to use the first non-null entry in the list.

SQLFiddle for MySQL implementation

share|improve this answer

Yes, you can do a self join, to the same table with another alias.

However, what you want to do looks like a hierarchy, or tree structure. Doing this through a parent or manager ID relation looks easy in the first place, but is a pain and heavy load when you want to retrieve for example the boss through multiple levels, or all employees under a certain manager.

You then need multiple queries or self join levels, not knowing how many.

Check for example Nested Sets or variants of Closure Tables as an alternative when implementing a hierarchy, then you have left/right values and between them all subordinates.

Nested Sets may be good for trees with mainly read access, closure tables have a lot of data (no problem for a company hierarchy) but are best when updated frequently.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.