mysql - 无法让 LEFT OUTER JOIN 通过相交表工作

Question

我有 2 个查找表和 1 个相交表，例如:

thing                  thing_feature                    feature
+----+-------------+   +----+----------+------------+   +----+-------------+
| id | name        |   | id | thing_id | feature_id |   | id | name        |
+----+-------------+   +----+----------+------------+   +----+-------------+
|  1 | Thing One   |   |  1 |        1 |          1 |   |  1 | Feature A   |
|  2 | Thing Two   |   |  2 |        2 |          1 |   |  2 | Feature B   |
|  3 | Thing Three |   |  3 |        2 |          2 |   |  3 | Feature C   |
|  4 | Thing Four  |   |  4 |        3 |          3 |   |  4 | Feature D   |
+----+-------------+   +----+----------+------------+   +----+-------------+

我不知道要生成的查询...

"Thing One", "Feature A"
"Thing Two", "Feature A"
"Thing Two", "Feature B"
"Thing Three", "Feature C"
"Thing Four", null

如果我停在相交表上，我可以让它包含“Thing 4”...

select
  thing.name,
  tf.id
from
  thing
  left outer join thing_feature tf on thing.id = tf.thing_id;

"Thing One", 1
"Thing Two", 1
"Thing Two", 2
"Thing Three", 3
"Thing Four", null

...但我无法弄清楚通过 thing_feature.feature_id 使查询“加入备份”到特征表的语法。我几乎要处理的查询丢失了无特征的“第四件事”。

select
  thing.id,
  thing.name,
  tf.id,
  feature.name
from
  thing
  left outer join thing_feature tf on thing.id = tf.thing_id,
  feature
where
  tf.feature_id = feature.id;

"Thing One", 1, "Feature 1"
"Thing Two", 1, "Feature 1"
"Thing Two", 2, "Feature 2"
"Thing Three", 3, "Feature 3"

也许是我深入 NoSQL 太久了。如果重要的话，这就是 MySQL。

这里没什么帮助？我需要对查询执行哪些操作才能包含“第四项”行？

Answer 1

你差不多了。您只需要再执行一次左外连接，以确保将所有结果保留在左表中，并在右表中获取所有空值。

select
  thing.name,
  f.name
from
  thing
  left outer join thing_feature tf on thing.id = tf.thing_id
  left outer join feature f on tf.feature_id = f.id;