List:General Discussion« Previous MessageNext Message »
From:Anoop kumar V Date:December 2 2007 12:31am
Subject:Re: Order table by org heirarchy (emp-mgr)
View as plain text  
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 <anoopkumarv@stripped> 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
>

Thread
Order table by org heirarchy (emp-mgr)Anoop kumar V1 Dec
  • Re: Order table by org heirarchy (emp-mgr)Anoop kumar V2 Dec
    • Re: Order table by org heirarchy (emp-mgr)Peter Brawley2 Dec