-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           |   |   |

I tried this but above rows are not printed.

select @ref:=id as id, unique_id, reference_id
from mytable
join (select @ref:=id from mytable WHERE id = 66)tmp
where reference_id=@ref

Demo on DB Fiddle

If it's possible I would like to edit this query into JPA?

Peter Penzov
  • 1,126
  • 134
  • 430
  • 808

1 Answers1

0

That's not possible with JPQL because this will be a recursive query.

But you can use SQL and execute this as native query.

entityManager.createNativeQuery("<your sql>");

Read more about recursive querying in the MariaDB manual https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82