Rewrite SQL query in CTE query

Question

I have this table which I would like to store a chain of records.

CREATE TABLE table_name (
    id INT,
    unique_id varchar,
    reference_id varchar,
);

I want to implement SQL query for MariDB which prints all records by id with all records with reference_id. Something like this:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |
| 99 | 454       | 33           |   |   |

I would like when I select record 66 to get all up and down transactions because each other are using id which points to them. How I can implement this using Recursive CTE? Is there a better way?

Expected result for record with unique_id 66:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 43           |   |   |
| 66 | 55655     | 45           |   |   |
| 78 | 88877     | 66           |   |   |

This SQL query works:

select t.id, t.unique_id, @uid := t.reference_id reference_id
from (select * from mytable order by id desc) t
join (select @uid := 66) tmp
where t.id = @uid or reference_id=66

https://www.db-fiddle.com/f/33mbBmQRiF5Z8eRoCR3oco/0

But when I tried this CTE query I can't get the above rows.

with recursive cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
select *
from cte; 

https://www.db-fiddle.com/f/ppmxX12PJ1rhszp85nJcWp/0

Can you give me a hand how to rewrite the SQL query properly, please?

Answer 1

You have to choose a direction when using the recursive CTE. You could add another recursive CTE to account for the descending reference_id's

with recursive 
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
SELECT *
FROM cte
UNION 
SELECT *
FROM cte2
ORDER BY id ASC;

Result

| id  | unique_id | reference_id |
| --- | --------- | ------------ |
| 43  | 55544     |              |
| 45  | 45454     | 43           |
| 66  | 55655     | 45           |
| 78  | 88877     | 66           |

DB-Fiddle

EDIT:

added version with UNION ALL

with recursive 
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.reference_id = 66
      union all
      select t.*
      from cte2 join
           mytable t
           on cte2.id = t.reference_id
     )
SELECT * 
FROM cte
UNION ALL
SELECT  *
FROM cte2
ORDER BY id ASC;

DB-Fiddle