Recursively select all Primary IDs, without stored procedure?

[dlite922]

I have a company hierarchy in a table that basically depicts who is who's boss.

Employee Table:

columns are:
id: primary key , auto_increment, integer
parentID: integer, default -1
level: integer, default 0 (0 = top level, CEO or President)
name: the employee's name.

So if John was Susan's boss. their values will look like this as an example:

for John:
id: 505
parentID: -1
level: 0

Susan:
id: 679
parentID: 505
level: 1


with me so far?

Now, since there are multiple hierarchies (ie Susan may be the boss of one or more persons) How can I get a list of the persons who are under John. (This would recursively be for all people under John and every employee under each one of those.

WHERE parentID = $val can only take me to one level.


Is there away to do recursion (or something) within a select statement or do i have code a stored procedure and install it with my app?

By the way, if you have another idea of how to setup this company employee hierarchy to achieve this, let me know. This model is not set in stone.

You guys always come through, thanks!




Dan // continues googling....

[Atli]

Hi.

With a nested structure like that, I can't see a way to select them all with just one query.
You would need a separate query for each "branch" of the tree structure, based on the data retrieved by the previous query.

I would do this using whichever API you are coding in. A nice little loop or a recursive function should do the trick.
It can be done with stored procedures, but I doubt it would be pretty. It would definitely require a temporary table, or some other way to cheat the fact that MySQL doesn't have a array type.