From: Peter Brawley
Date: December 2 2007 5:34pm
Subject: Re: Order table by org heirarchy (emp-mgr)
List-Archive: http://lists.mysql.com/mysql/210302
Message-Id: <4752ECA8.3070803@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------030403050209020201020209"

--------------030403050209020201020209
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Anoop,

It's an edge list tree, so unless you can specify max recursion depth, 
you need an sproc. See listing 7c at 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.

PB

Anoop kumar V wrote:
> 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
>>
>>     
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition. 
> Version: 7.5.503 / Virus Database: 269.16.13/1164 - Release Date: 12/2/2007 11:30 AM
>   

--------------030403050209020201020209--