Recursive data on MySQL 5.5

Question

I have a table structure (that cannot be changed) like this:

CREATE TABLE people(id INT PRIMARY KEY, name VARCHAR(150));
CREATE TABLE positions(id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE positionAssignments(id INT PRIMARY KEY, fkPosition INT,
    fkAssignedPerson INT, fkAssignedPosition INT, startDate DATETIME, 
    endDate DATETIME,
    FOREIGN KEY(fkAssignedPerson) REFERENCES people(id),
    FOREIGN KEY(fkAssignedPosition REFERENCES positions(id));

Being in positionAssignments:

fkPosition is which position is assigned to the person, or another position.
fkAssignedPerson the person assigned to the position.
fkAssignedPosition the position assigned to another.

My problem is that I need to obtain all positions of a person. As this structure can be recursive, I need create a SQL that returns up to 5 levels depth. Data can be as follows:

people                positions
| id | name    |        | id | name        |
+----+---------+        +----+-------------+
| 1  | Alice   |        | 10 | Position 1  |
+----+---------+        +----+-------------+
| 2  | Bob     |        | 20 | Position 2  |
+----+---------+        +----+-------------+
| 3  | Charlie |        | 30 | Position 3  |
+----+---------+        +----+-------------+
| 4  | Daniel  |        | 40 | Position 4  |
+----+---------+        +----+-------------+
                        | 50 | Position 5  |
                        +----+-------------+
                        | 60 | Position 6  |
                        +----+-------------+                            

positionAssignments
| id | fkPosition | fkAssignedPerson | fkAssignedPosition| startDate  | endDate |
+----+------------+------------------+-------------------+------------+---------+
| 1  | 10         | 1                | null              | 2018-01-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 2  | 20         | 2                | null              | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 3  | 20         | null             | 30                | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 4  | 30         | 3                | null              | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 5  | 40         | null             | 20                | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 6  | 50         | null             | 40                | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 7  | 60         | null             | 50                | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 8  | 60         | null             | 10                | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+
| 9  | 60         | 4                | null              | 2018-02-01 | null    |
+----+------------+------------------+-------------------+------------+---------+

Whis this structure, Alice belongs to Position 1, Bob belongs to Position 2 and Charlie to Position 2 and 3.

My question: I've done a SQL obtaining all positions of a person using UNION like this:

SELECT pe.id, pa1.fkPosition, pa1.id, pa1.startDate, pa1.endDate
FROM people pe
    INNER JOIN positionAssignments pa1
        ON pe.id = pa1.fkAssignedPerson
UNION
SELECT pe.id, pa2.fkPosition, pa2.id, pa2.startDate, pa2.endDate
FROM people pe
    INNER JOIN positionAssignments pa1
        ON pe.id = pa1.fkAssignedPerson
    INNER JOIN positionAssignments pa2
        ON pa1.fkPosition = pa2.fkAssignedPosition
-- And so on...

Is there any better way to do it, having in mind that I use MySQL 5.5 and I'd want to store this in a view (that should use merge algorithm)?
A desired output would be:

Expected Result
| idPerson | idPosition | idAssignment | startDate  | endDate |
+----------+------------+--------------+------------+---------+
| 1        | 10         | 1            | 2018-01-01 | null    |
+----------+------------+--------------+------------+---------+
| 1        | 60         | 8            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 2        | 20         | 2            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 2        | 40         | 5            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 2        | 50         | 6            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 2        | 60         | 7            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 3        | 20         | 3            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 3        | 30         | 4            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 3        | 40         | 5            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 3        | 50         | 6            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 3        | 60         | 7            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+
| 4        | 60         | 9            | 2018-02-01 | null    |
+----------+------------+--------------+------------+---------+

EDIT: Added new test data

Have you seen https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Caius Jard, Nov 15 '18 at 09:55
@CaiusJard Yes, but I think answers aren't applicable to this question. — Shirkam, Nov 15 '18 at 10:07
Sorry, i just picked on the recursion element, but having re-reviewed this I can't see how it's recursive from the data shown — Caius Jard, Nov 15 '18 at 10:18
@RaymondNijland Because *Charlie* is assigned to Position 3 in `positionAssignment` row 4 — Shirkam, Nov 15 '18 at 10:32
What is the usage of `fkAssignedPosition` ? How is Charlie on 2 positions. `fkAssignedPerson` value is 3 only in one of the rows. — Madhur Bhaiya, Nov 15 '18 at 13:00
@MadhurBhaiya Charlie belongs to `Position 3` and `Position 3` belongs to `Position 2`, so Charlie also belongs to `Position 2` — Shirkam, Nov 15 '18 at 16:41
@Shirkam I believe that the sample data is not enough. Can you enhance your sample data to consider upto 4 levels. — Madhur Bhaiya, Nov 15 '18 at 16:45
@Shirkam This is a recursive hierarchical query problem. In newer versions of MySQL, CTEs can be used. However, in your case, it can be solved using multiple self-joins, if you are only concerned upto 5 levels, right ? — Madhur Bhaiya, Nov 17 '18 at 08:03
@MadhurBhaiya Yes, you are right, but I don't see how can I return that result table with self joins — Shirkam, Nov 19 '18 at 13:49

Recursive data on MySQL 5.5

0 Answers0