Selecting chain of tuples in MySQL, Adjacency List?

Question

I have a table transaction:

ID          AMOUNT      PARENT_ID
8135124     1,56        NULL            | ON CHAIN 1
8135125     0,02        8135124         | ON CHAIN 1
8135126     1,09        8135124         | ON CHAIN 1
8135127     0,03        8135126         | ON CHAIN 1
8135128     0,47        8135124         | ON CHAIN 1

8135129     1,00        NULL            | OTHER CHAIN
8135130     0,01        8135129         | OTHER CHAIN

I'm trying without success get the complete chain of a given transaction. For example, when give 8135126 the return of the query must be all columns of transaction of 8135124, 8135125, 8135126, 8135127 and 8135128 transactions. The same result when give 8135125 or any other link from chain.

Notice that 8135125, 8135126, and 8135128 are children of 8135124 but, 8135127 is child of 8135126.

How to make it in MySQL?

See the answer here: http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree — Digital Chris, Jul 30 '14 at 17:52
It's seems to me a Adjacency List. But I not found a way to implements it with MySQL. — Maykonn, Jul 30 '14 at 18:31

Micromega · Answer 1 · 2014-07-31T14:35:57.983

1

With 3-4 levels you can use self joins. Unfortunately MySQL doesn't support recursive functions.

edited Jul 31 '14 at 14:35

answered Jul 30 '14 at 21:58

Micromega

12,486
7
35
72

not necessarily have only 3 levels, the depth of the branches can extend far. – Maykonn Jul 31 '14 at 14:07
3-4 levels. BTW. If my answer is helpful please consider to accept it. – Micromega Jul 31 '14 at 14:36
My first attempt was to solve with JOINs and too with UNION, but it did not work. Thanks! But, +1 =) – Maykonn Jul 31 '14 at 14:48

Maykonn · Accepted Answer · 2014-08-09T16:22:17.320

1

Could not solve with SQL, had to appeal to the language of the application. Was the only solution that met several loops on SQLs.

edited Aug 09 '14 at 16:22

answered Aug 09 '14 at 16:12

Maykonn

2,738
6
33
51

Selecting chain of tuples in MySQL, Adjacency List?

2 Answers2