连接表两次 - 在同一个表的两个不同列上 [英] Join table twice - on two different columns of the same table

问题描述

我有一个非常令人困惑的数据库，其中有一个表格，我需要在一个单独的表格中存储两个值。这是我的问题：

pre code $ Table
$ id
$ b Table2
- id
- table1_id
- table3_id_1
- table3_id_2

Table3
- id
- value

我需要从table1开始，并进行一个连接，使得我从 table3 在两个单独的列。所以我想要这样的东西：

  table1.id | table2.id | table2.table3_id_1 | table2.table3_id_2 | X | Y 
  

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

可能使它们成为变量或其他东西，所以我可以在 WHERE 子句中过滤它们。 b $ b

解决方案

  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; 
  

不需要加入 table1 。 table2 有你所需要的 - 假设有一个 t2.table1_id 实际存在于 table1 ）。否则，您可能想加入 table1 ，从而只选择 table1 中的行。 b

我使用 LEFT [OUTER] JOIN （而不是 [INNER] JOIN ）加入对于 table3 这两个实例，出于类似的原因：目前还不清楚参照完整性是否得到保证 - 是否任何关键列可以是NULL。一个 [INNER] JOIN 会从找不到匹配的结果中删除行。我假设你宁愿显示任何缺少 x 或 y的 NULL 值的行。

和 table3.id 需要 UNIQUE ，或者我们可以从每个 LEFT JOIN 中添加几个匹配的行：

$ ul b $ b

两个SQL LEFT JOINS产生不正确的结果

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

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.

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

• Two SQL LEFT JOINS produce incorrect result

这篇关于连接表两次 - 在同一个表的两个不同列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！