0

I've searched a lot to find my answer, the most related one is Finding all parents in mysql table with single query (Recursive Query)

The answer is great, but I found that this solution is not working when one node has more than one parent. Table like this:

id, node_id, parent_id
 1,       1,         2
 2,       2,         3    <-----
 3,       2,         4    <-----
 4,       4,         5
 5,       5,         6
 6,       6,         7

Notice that the line 2 and line 3, the node which has node_id=2 has more than one parent.

The answer mentioned above will only return parent id of 2, 3, 4 when node_id=1, which is not true. (Should be 2, 3, 4, 5, 6, 7)

callofdutyops
  • 318
  • 3
  • 9
  • 1
    MySQL 5.6 does not support recursive queries, so you cannot do this (safely) with a single select. You can use a recursive stored procedure, change the data model (to include the complete path in each row), or upgrade to MySQL 8+. – Gordon Linoff Jan 13 '20 at 11:27
  • *I've searched a lot to find my answer, the most related one is Finding all parents in mysql table with single query (Recursive Query)* This is fake solution, it requires `parent_id < id` strongly (or backward) for all records in addition to "only one parent" relation. – Akina Jan 13 '20 at 11:35

0 Answers0