sql - 如何在 JOIN-ed 表上执行 JOIN？

Question

我需要在 JOIN-ed 表上执行 JOIN，但我不确定如何完成它。希望下面的查询演示了我正在尝试做的事情:获取国家名称、每个国家的领导人和每个领导人的家乡。

我认为此查询的问题在于在与 President.HomeTown_Id 的第二次连接中使用了连接表“President”。我不知道还能尝试什么。

SELECT 
  Countries.Name AS Country, 
  President.Name AS Leader,
  PresidentHomeTown.Name AS LeaderHomeTown
FROM Countries
  LEFT OUTER JOIN PoliticalFigures AS President ON Countries.President_Id = President.Id
  LEFT OUTER JOIN Cities AS PresidentHomeTown ON President.HomeTown_Id = PresidentHomeTown.Id

在 VS 中，我收到错误消息“无法绑定(bind)由多部分组成的标识符“President.Id”。”

表名和字段名是虚构的，但我需要解决一个相同的问题。我更改了名称以使事情更清楚；希望这将与更多人相关。

-- 更新--

也许原始代码有帮助:

SELECT 
    CaseComparisons.Directory AS CaseComparisonDir, 
    BaselineResult.Directory AS BaselineResultDir, 
    ComparisonResult.Directory AS ComparisonResultDir,
    Setup.FullSvnLink AS SvnLink,
    BaselineVersion.FullFilePath AS BaselineExecutableDir
FROM CaseComparisons 
    LEFT OUTER JOIN Results AS BaselineResult ON CaseComparisons.BaselineResult_Id = Baseline.Id 
    LEFT OUTER JOIN Results AS ComparisonResult ON CaseComparisons.ComparisonResult_Id = Comparison.Id
    LEFT OUTER JOIN Setups AS Setup ON Baseline.Setup_Id = Setups.Id
    LEFT OUTER JOIN BuildVersions AS BaselineVersion ON BaselineResult.Version_Id = BuildVersions.Id
WHERE 
    CaseComparisons.Status = 'Queued' OR 
    Baseline.Status = 'Queued' OR 
    Comparison.Status = 'Queued'

运行查询时出现的错误:

The multi-part identifier "Baseline.Id" could not be bound.
The multi-part identifier "Comparison.Id" could not be bound.
The multi-part identifier "Baseline.Setup_Id" could not be bound.
The multi-part identifier "Setups.Id" could not be bound.
The multi-part identifier "BuildVersions.Id" could not be bound.
The multi-part identifier "Baseline.Status" could not be bound.

Answer 1

您指定的别名(例如在 Results AS ComparisonResult 中)与您尝试使用的别名(例如在 Comparison.Id 中)不匹配>).所以，改变这个:

    LEFT OUTER JOIN Results AS ComparisonResult
                 ON CaseComparisons.ComparisonResult_Id = Comparison.Id

要么这样:

    LEFT OUTER JOIN Results AS ComparisonResult
                 ON CaseComparisons.ComparisonResult_Id = ComparisonResult.Id

或者这个:

    LEFT OUTER JOIN Results AS Comparison
                 ON CaseComparisons.ComparisonResult_Id = Comparison.Id

(对于所有其他联接也类似)。