 | | | Order table by org heirarchy (emp-mgr) | Order table by org heirarchy (emp-mgr) 2007-12-01 - By Anoop kumar V
Back
Hi Experts,
I need a small help - I think the solution is not complex, but I am not sure
where to start.
Here is my problem. I have a table that defines the employee to manager
relationship. This table will interface with another system and the users
would be created in the other system. There will be a pointer to the user
who is the manager of the currently processed user. So if the manager user
has not yet been created, the current user process will not complete as the
pointer to the manager-user does not exist.
So I need to ensure that all managers are created first before creating the
users. But managers are also like other users in the same table in the emp
column.
Here is a sample of the table:
mysql> select * from emp;
+-- ---+-- ---- ---- ------+-- ---+
| id | name | mgr |
+-- ---+-- ---- ---- ------+-- ---+
| 1006 | Derek Smalls | 1003 |
| 1005 | Nigel Tufnel | 1003 |
| 1004 | David St. Hubbins | 1003 |
| 1003 | Ian Faith | 1002 |
| 1002 | Bobbi Flekman | 1001 |
+-- ---+-- ---- ---- ------+-- ---+
6 rows in set (0.00 sec)
I need to sort this table to look like this:
+-- ---+-- ---- ---- ------+-- ---+
| id | name | mgr |
+-- ---+-- ---- ---- ------+-- ---+
| 1001 | Denis Eaton-Hogg | NULL |
| 1002 | Bobbi Flekman | 1001 |
| 1003 | Ian Faith | 1002 |
| 1001 | Denis Eaton-Hogg | NULL |
| 1004 | David St. Hubbins | 1003 |
| 1005 | Nigel Tufnel | 1003 |
| 1006 | Derek Smalls | 1003 |
+-- ---+-- ---- ---- ------+-- ---+
See how 1001 is the manager of everybody - so I can create this user first,
that would take care of 1002 as it would contain a link to the 1001 user and
all would be good. So on for 1002 and 1003....
I think I need to do a self join and order by - if you could even give me a
hint that would be most helpful.
Thanks,
Anoop
|
|
|