How to join a table that has no relationship to its parent tables?-CodePudding

Suppose I have the following schema:

-- table 1
__________
|   id    |
|   1000  |
|   1001  |
|   1002  |
|   1003  |

-- table 2
______________________
|  id  |   fk_table_1 |
|  100 |       1000   |
|  101 |       1000   |
|  102 |       1000   |
|  103 |       1000   |
|  104 |       1001   |


-- table 3
_______________________________
|  id  |   source  |   target  |
|  1   |    100    |    101    |
|  2   |    102    |    103    |

I need to pull out all the table 3 records that are relevant to table 1 and table 2. The problem here is that table 3 has no relationship to the other tables.

How can I join these tables?

The desired output would be something like:

relations: [
    {
        id: '1',
        source: '100',
        target: '101'
    },
    {
        id: '2',
        source: '102',
        target: '103'
    },
]

This array must contain all table 2 records that are related to table 1, in this case, the result is linked to the record with id 1000 from table 1.

I've tried doing,

SELECT
     tb3.id,
     tb3.source,
     tb3.target
FROM table_3 tb3
LEFT JOIN table_1 tb1 ON tb1.id = 1000
LEFT JOIN table_2 tb2 ON tb2.fk_table_1 = tb1.id

But this just returns all records from table 3.

CodePudding user response：

From what I understand you just want to show table3 rows where source and target are linked to table1.id = 1000.

You can join

select t3.*
from table3 t3
join table2 t2s on t2s.id = t3.source and t2s.fk_table_1 = 1000
join table2 t2t on t2t.id = t3.target and t2t.fk_table_1 = 1000;

or use IN (which I prefer, because we only want to select table3 rows for which a condition applies, and conditions belong in the WHERE clause, not in the FROM clause in my opinion)

select *
from table3
where source in (select id from table2 where fk_table_1 = 1000)
  and target in (select id from table2 where fk_table_1 = 1000);

If it suffices that source or target are linked to t1.id 1000, then the second query can be easily amended by changing AND to OR. (The first query would need more amendment, i.e. changing inner to outer joins and applying a combined WHERE clause.)

CodePudding user response：

You can use json_agg with json_build_object:

select json_agg(json_build_object('id', t3.id, 'source', t3.source, 'target', t3.target, 'fk_table_1', t2.fk_table_1)) 
from table3 t3 join table2 t2 on t3.source = t2.id where t2.fk_table_id = 1000

Output:

[{"id" : 1, "source" : 100, "target" : 101, "fk_table_1" : 1000}, {"id" : 2, "source" : 102, "target" : 103, "fk_table_1" : 1000}]