From: Anoop kumar V (anoopkumarvgmail.com)
Date: Sat Dec 01 2007 - 18:31:46 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <anoopkumarvgmail.com> 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
>

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]