Sorry that I have not put code but I have searched and I have not been able to approach the subject. I have a table that consists of: id, name, position, idboss. the latter is included in the same table but I want to make a loop that shows me the hierarchy of each person example: the hierarchy is made up of (from least to most): auditor=>supervisor=>coordinator=>director and the loop What I need is for the immediate boss of each person to bring me to the superior, who in this case is the director; I have put an image with the result of what I want you to consult me.
If I have understood correctly what you want, I would do a rearrangement of my database design, with several tables:
empleado
: to manage all employees and their positionscargo
: to handle chargesgestion
: to handle relationships between employees. In other words, who is the boss of whom and in what sense?tipo_gestion
: to manage the type of command that one employee exercises over another and the priority of that command in the organization chart. This table will allow ordering according to importance from director to the last of the company.Based on that schema, we could get what you want with a simple query like this:
A hypothetical result would be:
What the hypothetical results express, I underline, is that:
Alberto López
is the oneDirector
ofPablo Contancio,Jeyson Vargas,Andrea Villamizar
Carolina Montes
isCoordinadora
fromJorge Rincón,Laura Acevedo
I want to state that a design like yours could be complicated . Let's imagine a scenario in which an employee can be subordinate to two or more bosses in different areas of the company. Or that an employee belongs
sub-alterno
to someone and at the same time is the boss of others... for all those complicated cases, the tablegestion
offers flexibility that adapts to a clear and easy handling of anything.I don't know if the answer is exactly what you want, but at least I think it opens up a different horizon for this type of case.
I hope it is useful for you. Below I leave you the link with the hypothetical data that I have used to make my demonstrations. You can modify them in rextester and make tests on that data model or modify it at your convenience.
proof of concept
VER DEMOSTRACIÓN EN REXTESTER
It depends on the database engine you are using if the supported language is Transact SQL like most, you could create a recursive scalar function that resolves that Query and call it in the SELECT, you don't specify what syntax you use and I don't remember much about TSQL so take this snippet as pseudocode.
And it would be called something like this
If the number of "levels" is fixed and they are not too many, according to your example, no more than 4, you could use a flat query, doing a
JOIN
recursive on the same table:Note: The condition of
T(n).ID <> T(n).idjefe
is to not continue advancing if we reach the director.