1

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?

3

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

  • Thanks, I have 2 additional questions: 1 Can I somehow use it in JPA? 2. Do you know what will be the performance impact for very large table? – Peter Penzov Feb 5 at 15:15
  • Which one will be faster CTE of the first solution with Joins? – Peter Penzov Feb 5 at 15:16
  • 1
    Yes , exactly . – ypercubeᵀᴹ Feb 5 at 15:57
  • Unfortunately I use 10.0.36-MariaDB-0ubuntu0.16.04.1 from official Ubuntu repository and I get You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'recursive cte as ( select t.* from payment_transactions t whe' at line 1 – Peter Penzov Feb 5 at 16:42
  • I updated MariaDB to version 10.3 and it works! Thanks! – Peter Penzov Feb 5 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.