加入表两次 - 在同一个表的两个不同列上 [英] Join table twice - on two different columns of the same table
问题描述
我有一个非常混乱的数据库,其中一个表在单独的表中保存了我需要的两个值.这是我的问题:
表1- ID表2- ID- table1_id- table3_id_1- table3_id_2表3- ID- 价值
我需要从 table1 开始并执行一个连接,以便在两个单独的列中返回来自 table3
的值.所以我想要这样的东西:
table1.id |table2.id |table2.table3_id_1 |table2.table3_id_2 |X |是
其中X
和Y
分别是table3_id_1
和table3_id_2
连接的行的值.p>
可能使它们成为变量或其他东西,以便我也可以在 WHERE
子句中过滤它们?
SELECT t2.table1_id, t2.id AS table2_id, t2.table3_id_1, t2.table3_id_2, t31.value AS x, t32.value AS y从表 2 t2LEFT JOIN table3 t31 ON t31.id = t2.table3_id_1LEFT JOIN table3 t32 ON t32.id = t2.table3_id_2;
没有必要涉及table1
.table2
有你需要的一切——假设有一个 外键约束 保证引用完整性(所有t2.table1_id
实际上都存在于table1
中).否则,您可能想要加入 table1
,从而仅选择也存在于 table1
中的行.
我使用 LEFT [OUTER]JOIN
(而不是 [INNER] JOIN
) 加入 table3
的两个实例,原因类似:尚不清楚是否保证引用完整性 - 以及是否有任何键列可以是 NULL
.[INNER] JOIN
将从结果中删除未找到匹配项的行.我假设您宁愿为任何缺少的 x
或 y
显示带有 NULL
值的此类行.
table3.id
必须是 UNIQUE
,否则我们可能会将行与每个 LEFT JOIN
的多个匹配项相乘:
I have a very confusing database with a table that holds two values I need in a separate table. Here is my issue:
Table1
- id
Table2
- id
- table1_id
- table3_id_1
- table3_id_2
Table3
- id
- value
I need to go from table1 and do a join that would give me back the value from table3
in two separate columns. So I want something like this:
table1.id | table2.id | table2.table3_id_1 | table2.table3_id_2 | X | Y
Where X
and Y
are the values for the row connected by table3_id_1
and table3_id_2
respectively.
Possibly make them variables or something so I can filter them in a WHERE
clause as well?
SELECT t2.table1_id
, t2.id AS table2_id
, t2.table3_id_1
, t2.table3_id_2
, t31.value AS x
, t32.value AS y
FROM table2 t2
LEFT JOIN table3 t31 ON t31.id = t2.table3_id_1
LEFT JOIN table3 t32 ON t32.id = t2.table3_id_2;
There is no need to involve table1
. table2
has all you need - assuming there is a foreign key constraint guaranteeing referential integrity (all t2.table1_id
are actually present in table1
). Else you may want to join to table1
, thereby selecting only rows also present in table1
.
I use LEFT [OUTER] JOIN
(and not [INNER] JOIN
) to join to both instances of table3
for a similar reason: it is unclear whether referential integrity is guaranteed - and whether any of the key columns can be NULL
. An [INNER] JOIN
would drop rows from the result where no match is found. I assume you would rather display such rows with a NULL
value for any missing x
or y
.
table3.id
needs to be UNIQUE
, or we might multiply rows with several matches from each LEFT JOIN
:
这篇关于加入表两次 - 在同一个表的两个不同列上的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!