sqlite - 用左外连接链接 3 个表

Question

我在 Android 应用程序的 SQLite 数据库中有 3 个表。下图显示了我正在使用的相关表格。

Tables

我试图分别从measurement_lines 和competences 中获取两个字段，value 和name，它们与测量中的特定person_id 相关联。我正在尝试进行返回这些字段的查询，但我运气不佳。到目前为止，我得到的最好的是以下查询:

SELECT name, value
FROM measurements, measurement_lines, competences
WHERE measurements.id = measurement_lines.measurements_id
AND measurement_lines.competences_id = competences.id
AND measurements.persons_id = 1

然而，这有一个问题。当一个人在测量中没有条目时，此查询将不会返回任何记录(随后，在测量行中也没有任何条目)。我想要的是始终获得能力名称列表，即使值列是空的。我猜我需要一个左外连接，但我似乎无法让它工作。以下查询不返回任何记录:

SELECT name, value 
FROM measurements AS m, competences AS c 
LEFT OUTER JOIN measurement_lines AS ml ON c._id = ml.competence_id   
WHERE ml.measurement_id = m._id AND m.persons_id = 1

Answer 1

对于内部连接，您可能会对连接条件和选择谓词之间的区别感到草率，但是当涉及到外部连接时，情况就会有所不同。 WHERE 中出现的任何标准子句在执行所有连接后过滤您的结果行(至少在逻辑上)，这可以删除与外部表关联的结果行。

此外，如果您不确定连接顺序，可以使用括号来明确您的意图。至少在许多 DBMS 中。它看起来像 SQLite 不支持它们。

看起来你可能想要这个:(编辑以避免使用括号)

SELECT c.name, pm.value
FROM competences c
  LEFT OUTER JOIN (
      SELECT ml.competences_id AS cid,
        ml.value AS value
      FROM measurement_lines ml
        INNER JOIN measurements m
        ON m.id = ml.measurements_id
      WHERE m.person_id = 1
  ) pm
  ON pm.cid = c.id