0

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 JPA query which prints all records by unique_id with all record reference_id. Something like this:

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

I would like when I select record 55544 to get all transactions because each other are using reference_id which points to them. How I can implement this JPA?

Expected result for record with unique_id 55544:

| id | unique_id | reference_id |   |   |
|----|-----------|--------------|---|---|
| 43 | 55544     |              |   |   |
| 45 | 45454     | 55544        |   |   |
| 66 | 55655     | 45454        |   |   |
| 78 | 88877     | 55655        |   |   |
Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
  • If you want a mysql query, then this is a duplicate question (search for mysql hiersrchical query, or listing all nodes in a tree) and the hql tag should be removed. If you want hql query, then mysql and sql tags should be removed. So, which case is it? – Shadow Jan 27 '19 at 08:36
  • Have you seen https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query (mysql)? – Caius Jard Jan 27 '19 at 09:00

0 Answers0