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 <[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
------------------------------------------------------------------------
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
