I have the following tables:


id entity_id value

Table2 - Same structure as Table1

id entity_id value


source_id destination_id entity_id

Table1 and Table2 are both partitions from the same table and Table3 contains the connections between ids on that partition's tables.

One simple example

id entity_id value table
1 1 1 table1
2 2 2 table2
source_id destination_id entity_id
1 2 1

In this example the two records are connected directly but it's possible for them to be connected indirectly for instance:

id entity_id value table
1 1 1 table1
2 2 2 table2
3 3 3 tablex
source_id destination_id entity_id
1 3 1
3 2 3

In this example, the values on Table1 and Table2 are connected via TableX and this can go any level deep.

Now I'm trying to do a query that would return in this case the rows from Table1 and Table2.

I did the following:

    SELECT t1.id, t1.entity_id, t1.value
    FROM table1 t1
    JOIN table3 t3 
    ON t1.id = t3.source_id
    SELECT j.id, j.entity_id, j.value
    FROM cte j
    JOIN table3 t3
    ON j.id = t3.source_id
    JOIN table2 t2 
    ON t2.id = t3.destination_id 

But it's only returning:

1   1   1

instead of or similar (I just need the table1 and table2 data)

1   1   1
2   2   2


Any help is welcome.


  • Please provide scripts to create the test tables and content please.
    – Peter
    Feb 15 at 15:49
  • @Peter done. dbfiddle.uk/yNLG9-ON
    – TheSQLGoat
    Feb 15 at 15:59
  • That design doesn't make much sense. You are keeping references to different tables in the same column. That alone is already an issue, making it impossible for you to define referential integrity on the database level. But you are making it even worse by having no indication which table each individual value is a reference to. Why?
    – Andriy M
    Feb 16 at 0:02
  • To clarify, I'm not asking just to bash your schema. I think the way you've designed it may be part of the reason why you are struggling with a solution. Knowing whether it has to be like that or not could inform potential answers.
    – Andriy M
    Feb 16 at 0:13
  • The entity_id refers to each partition the connection belongs to. I know it's bad design but unfortunately can't be changed.
    – TheSQLGoat
    Feb 16 at 11:06


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.