CODEWITHSUNDEEP
sql
Cheknov
Cheknov

Reputation: 2082

Join two tables through a third relationship table that relates the first two but whose id are different

Normally, when the id's match, we could do something like this:

SELECT Table1.somecol, Table2.somecol
FROM Table3
    INNER JOIN Table1
        ON Table3.Id = Table1.Id
    INNER JOIN Action 
        ON Table3.Id = Table2.Id

The problem is that I have the following situation where id's don't match:

Table 1:

+-----------+------------+------------+------------+
| record_id |     A      |     B      |     C      |
+-----------+------------+------------+------------+
|       180 | some value | some value | some value |
|       214 | some value | some value | some value |
|       243 | some value | some value | some value |
+-----------+------------+------------+------------+

Table 2:

+-----------+------------+
| record_id |     D      |
+-----------+------------+
|      5798 | some value |
|      6135 | some value |
|      6135 | some value |
+-----------+------------+

Table 3 (to relate tables 1 and 2):

+-----------+-----------+-----------+
| record_id | table2_id | table1_id |
+-----------+-----------+-----------+
|     15497 |      5798 |       180 |
|     15621 |      6135 |       214 |
|     15622 |      6135 |       243 |
+-----------+-----------+-----------+

Desired result:

+-----------+------------+------------+------------+-----------+------------+
| record_id |     A      |     B      |     C      | record_id |     D      |
+-----------+------------+------------+------------+-----------+------------+
|       180 | some value | some value | some value |      5798 | some value |
|       214 | some value | some value | some value |      6135 | some value |
|       243 | some value | some value | some value |      6135 | some value |
+-----------+------------+------------+------------+-----------+------------+

Does anyone know how I could get the desired result?

Upvotes: 0

Views: 24

Answers (1)

eshirvana
eshirvana

Reputation: 24603

so you want to join all together based on table3 , your join conditions in your query doesn't look right ( based on sample data):

select a.* , b.* 
from table3 c
join table1 a on a.record_id = c.table1_id
join table2 b on b.record_id = c.table2_id

Upvotes: 1

Related Questions