The resultant table should be this: +------+-------------------+------+ | id | name | mgr | +------+-------------------+------+ | 1001 | Denis Eaton-Hogg | NULL | | 1002 | Bobbi Flekman | 1001 | | 1003 | Ian Faith | 1002 | | 1004 | David St. Hubbins | 1003 | | 1005 | Nigel Tufnel | 1003 | | 1006 | Derek Smalls | 1003 | +------+-------------------+------+
The number happened to be sorted here - but that may not be the case - there are new manager hires etc.. Thanks. On Dec 1, 2007 4:48 PM, Anoop kumar V <[EMAIL PROTECTED]> wrote: > 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 >
