如何在JOIN

如何在JOIN-ed表上执行JOIN？(How can I perform a JOIN on a JOIN-ed table?)

我需要在JOIN-ed表上执行JOIN，我不知道如何完成它。 希望下面的查询能说明我要做的事情：获取国家名称，每个国家的领导者以及每个领导者的家乡。

我认为这个查询的问题是在与President.HomeTown_Id的第二次连接中使用JOIN-ed表“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中，我收到错误，“多部分标识符”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.

I need to perform a JOIN on a JOIN-ed table, and I'm not sure how to accomplish it. Hopefully, the query below demonstrates what I'm trying to do: get country names, each country's leader, and each leader's home town.

I think the problem with this query is using the JOIN-ed table "President" in the second join with President.HomeTown_Id. I don't know what else to try.

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

In VS, I'm getting the error, "The multi-part identifier "President.Id" could not be bound."

The names of tables and fields are fictitious, but I need to solve an identical problem. I changed the names to make things clearer; hopefully this will be relevant to more people.

-- update --

Maybe the original code helps:

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 errors I get when I run the query:

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.

最满意答案

您指定的别名（例如，在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

（同样适用于所有其他连接）。

You've got mismatches between the aliases you're specifying (e.g. in Results AS ComparisonResult) and the aliases you're trying to use (e.g. in Comparison.Id). So, change this:

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

to either this:

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

or this:

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

(and similarly for all the other joins).