通过第三个关系表连接两个表，该关系表与前两个表相关，但id不同

Question

通常，当id匹配时，我们可以做这样的事情：

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

问题是，我遇到了以下id不匹配的情况：

表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 |
+-----------+------------+------------+------------+

表2:

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

表3(与表1和表2相关(：

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

期望结果：

+-----------+------------+------------+------------+-----------+------------+
| 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 |
+-----------+------------+------------+------------+-----------+------------+

有人知道我怎样才能得到想要的结果吗？

Answer 1

所以你想根据表3把所有的连接在一起，你的查询中的连接条件看起来不合适(基于样本数据(：

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