I have the following tables within a database:
+-------------+ +-------------+-------------+ +---------------------------+
| project1 | | project2 + | project3 |
+-------------+ +-------------+-------------+ +---------------------------+
| project1_id | | project2_id | project1_id | | project3_id | project2_id |
+-------------+ +-------------+-------------+ +---------------------------+
| hnc1 | | hnc4 | hnc1 | | hnc7 | hnc4 |
| hnc2 | | hnc5 | hnc1 | | hnc8 | hnc4 |
| hnc3 | | hnc6 | hnc3 | | hnc9 | hnc6 |
+-------------+ +-------------+-------------+ +---------------------------+
I need to retrieve all descendants of a given ID from all three tables - including the ID itself if it is found in any table.
For example, if I query using the ID 'hnc4' it should get:
+-------------+
| descendants |
+-------------+
| hnc7 |
| hnc8 |
| hnc4 |
+-------------+
If I query using the ID 'hnc1':
+-------------+
| descendants |
+-------------+
| hnc4 |
| hnc5 |
| hnc7 |
| hnc8 |
| hnc1 |
+-------------+
The query must work even if the ID is only in the last table project3
.
I was searching and found that a recursive CTE could be useful but I don't know how to make it with 3 tables involved.
Any guidance on how to get with the right results?
CREATE TABLE
statements. And always disclose your version of Postgres.