Assuming that I have a sql table named table_1 that has two columns named "ref_1_id" and "ref_2_id" which both reference table_2:
table1: (id, ref_1_id, ref_2_id)
and inside table_2, I have a reference to table_3 as ref_3_id:
table_2: (id, ref_3_id)
and in table_3 I have a col named as cost:
table_3: (id, cost)
I would like to write a sql query to fetch table_1 id as well as the cost fetched from table_3 corresponding to both ref_1_id and ref_2_id: (table_1_id, cost_1, cost_2)
You can start by trying a lot of JOINs.
SELECT t1.id AS table_1_id, t31.cost AS cost_1, t32.cost AS cost_2
FROM table_1 t1
JOIN table_2 t21 ON t1.ref_1_id = t21.id
JOIN table_2 t22 ON t1.ref_2_id = t22.id
JOIN table_3 t31 ON t21.ref_3_id = t31.id
JOIN table_3 t32 ON t22.ref_3_id = t32.id
If a large number of JOINs is not satisfactory by performance. You may need to denormalize the database. For example, create fields in table 1 or 2 that copy "cost" from table 3. Or merge some of these tables into one.
