MySQL query to follow relationship?

Question

I am asking the more experienced here if there is a way of creating a query to follow a relationship in a table.

For example, I have a table of people, for each person there is a column to show who their supervisor is, which is the id of another person in the same table.

So table 'people' will have:

id, firstname, lastname, supervisor.

Is there a way to create a query that will return a list of all the supervisors, until there isn't anymore, and all the people that report to them (and their reports) until there isn't anymore?

My thoughts at the moment are that I will need a while loop in PHP for the supervisors (which is always one to one) and a while loop for the predecessors, which will potentially be many, so will need a while loop within that to go through all the iterations.

Then essentially put all of that into an array for display.

My concern is that could be a server intensive process and be quite slow.

Is there a MySQL query that can follow virtual breadcrumbs and return results?

Appreciate any assistance.

You managed to mention "people", "user" and "person" in one sentence. I think they're all the same thing, but I can't be sure. — KIKO Software, Dec 05 '22 at 11:59
removed the word user, people was the table, person is essentially each row of the people table. — RBurns, Dec 05 '22 at 13:57
Just for others, the link @Quentin provided, gave the solution. Thank you! — RBurns, Jan 09 '23 at 14:02

score 0 · Answer 1 · answered Dec 06 '22 at 02:01

SELECT  s.supervisor,
        e.last_name,
        e.first_name
    FROM people AS e
    JOIN people AS s  ON s.id = e.supervisor
    ORDER BY s.supervisor, e.last_name, e.first_name

or

SELECT  s.supervisor AS Boss
        GROUP_CONCAT(e.last_name) AS Reports
    FROM people AS e
    JOIN people AS s  ON s.id = e.supervisor
    GROUP BY s.supervisor
    ORDER BY s.supervisor

MySQL query to follow relationship?

1 Answers1