连接表两次 - 在同一个表的两个不同列上

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开始并进行一个连接，它会在两个单独的列中返回table3中的值。所以我想要这样的东西：

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.

其中X和Y分别是由table3_id_1和table3_id_2连接的行的值。

Possibly make them variables or something so I can filter them in a WHERE clause as well?

可能使它们变量或其他东西，所以我也可以在WHERE子句中过滤它们？

3 个解决方案

#1

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 join in 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 present in table1.

没有必要加入table1。 table2拥有你所需要的一切 - 假设有一个保证参照完整性的外键约束（所有t2.table1_id实际上都存在于table1中）。否则，您可能希望加入table1，从而仅选择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 - ans 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.

我使用LEFT [OUTER] JOIN（而不是[INNER] JOIN）连接到table3的两个实例，原因类似：不清楚是否保证参照完整性 - 是否任何键列都可以为NULL。 [INNER] JOIN将从未找到匹配项的结果中删除行。我假设你宁愿显示任何缺少x或y的NULL值。

And table3.id needs to be UNIQUE, or we might multiply rows with several matches from each LEFT JOIN:

并且table3.id需要是UNIQUE，或者我们可以将来自每个LEFT JOIN的多个匹配的行相乘：

Two SQL LEFT JOINS produce incorrect result
两个SQL LEFT JOINS产生不正确的结果

#2

If you join a table several times, use aliases to distinguish them:

如果多次连接表，请使用别名来区分它们：

SELECT table1.id,table2.id,table2.table3_id_1,table2.table3_id_2,t3_1.id,t3_2.id
FROM table1
JOIN table2 ON table1.id=table2.table1_id
JOIN table3 t3_1 ON table2.table3_id_1=t3_1.id
JOIN table3 t3_2 ON table2.table3_id_2=t3_2.id
WHERE ... t3_1.id=... AND ... t3_2.id=...

#3

select t1.id as table1_id, 
    t2.id as table2_id, 
    t2.table3_id_1, 
    t2.table3_id_2,
    t3_1.value as X, 
    t3_2.value as Y
from Table1 t1
inner join Table2 t2 on t1.id = t2.table1_id
inner join Table3 t3_1 on t2.table3_id_1 = t3_1.id
inner join Table3 t3_2 on t2.table3_id_2 = t3_2.id
where t3_1.value = 'some_value'
    or t3_2.value = 'some_other_value'

#1

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;