Recursive CTE to traverse a hierarchy with dual descendant IDs-CodePudding

Given the following table of sports matches with two players:

match_id	match_date	p1_id	p2_id
1	01/01/2022	1	2
2	02/01/2022	3	1
3	03/01/2022	3	4
4	04/01/2022	2	3
5	05/01/2022	5	6
6	06/01/2022	1	2
7	07/01/2022	3	1
8	08/01/2022	3	4
9	09/01/2022	2	3
10	10/01/2022	5	6
11	11/01/2022	3	4
12	12/01/2022	7	8
13	13/01/2022	3	1
14	14/01/2022	5	7
15	15/01/2022	4	5

I’m trying to write a query with a recursive CTE that when given a match_id the query will return all match_id values for future matches for each of the two players. The recursion is needed because I need the query to also include all future matches for any of the future matches' players.

Using the example above and match_id = 6 then the two player IDs are 1 and 2. I need the query to return all future matches for these player IDs. This means the query needs to return 7, 9 and 13. However, in match_id = 7 player ID 1 plays player ID 3 so now all of their future match_id values from that point also need to be included. This means the query also needs to return 8 and 11. In match_id = 8 and match_id = 11 player ID 3 plays player ID 4 so the final match_id to be returned is 15.

The expected output is as follows:

match_id
7
8
9
11
13
15

I've written the following query:

WITH RECURSIVE match_ids AS (
    SELECT
        m1.match_id,
        m1.match_date,
        m1.p1_id,
        m1.p2_id
    FROM recursive_test AS m1
    WHERE m1.match_id = 6
    UNION ALL
    SELECT
        m2.match_id,
        m2.match_date,
        m2.p1_id,
        m2.p2_id
    FROM recursive_test AS m2
    INNER JOIN match_ids
        ON (
            match_ids.p1_id = m2.p1_id
            OR match_ids.p1_id = m2.p2_id
            OR match_ids.p2_id = m2.p1_id
            OR match_ids.p2_id = m2.p2_id
        )
            AND match_ids.match_date > m2.match_date
)
SELECT match_id
FROM match_ids

However, this returns:

match_id
6
2
4
1
1
2
3
1
2
1

Where might I be going wrong?

Here's the SQL to create the table:

CREATE TABLE `recursive_test` (
  `match_id` int NOT NULL,
  `match_date` date NOT NULL,
  `p1_id` int NOT NULL,
  `p2_id` int NOT NULL,
  PRIMARY KEY (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `recursive_test` VALUES (1,'2022-01-01',1,2),(2,'2022-01-02',3,1),(3,'2022-01-03',3,4),(4,'2022-01-04',2,3),(5,'2022-01-05',5,6),(6,'2022-01-06',1,2),(7,'2022-01-07',3,1),(8,'2022-01-08',3,4),(9,'2022-01-09',2,3),(10,'2022-01-10',5,6),(11,'2022-01-11',3,4),(12,'2022-01-12',7,8),(13,'2022-01-13',3,1),(14,'2022-01-14',5,7),(15,'2022-01-15',4,5);

CodePudding user response：

WITH RECURSIVE
cte AS (
    SELECT *
    FROM recursive_test
    WHERE match_id = @starting_match_id
  UNION ALL
    SELECT recursive_test.*
    FROM recursive_test
    JOIN cte ON recursive_test.match_date > cte.match_date
    WHERE recursive_test.p1_id IN (cte.p1_id, cte.p2_id)
       OR recursive_test.p2_id IN (cte.p1_id, cte.p2_id)
)
SELECT DISTINCT * 
FROM cte;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6ca1e57845bae995bacb04455beb6340