我有这样一个表结构（不能更改）：

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));

处于 positionAssignments : fkPosition 是分配给该人员的职位，还是另一个职位。 fkAssignedPerson 分配给这个职位的人。 fkAssignedPosition 分配给另一个人的职位。
我的问题是我需要获得一个人的所有职位。由于这个结构可以是递归的，所以我需要创建一个返回5级深度的sql。数据如下：

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

在这个结构中，爱丽丝属于位置1，鲍勃属于位置2，查理属于位置2和3。
我的问题是：我已经做了一个sql获取所有职位的人使用 UNION 这样地：

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

有没有更好的方法来做到这一点，记住我使用的是mysql 5.5，并且我希望将其存储在一个视图中（应该使用mergealgorithm）？
期望的输出是：

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

编辑：添加新的测试数据