无法绑定多部分标识符

I am trying to return a few rows from my tables Jobs, but also the next due date from the table JobProducts (where each JobProduct has a due date).

我试图从我的表作业返回几行，但也要返回表JobProducts的下一个到期日期(每个JobProduct都有一个到期日)。

I have the following so far

到目前为止，我有以下几点

SELECT  J.CustomerID,  J.JobID, J.Status, 
J.Deleted, J.JobNo, Customers.CompanyName AS [Company], 
J.DateCreated AS [Date Created], derivedtbl_1.DueDate AS [Due Date] 
FROM Jobs  J
LEFT OUTER JOIN 
Customers ON J.CustomerID = Customers.CustomerID CROSS JOIN 
(SELECT   TOP (1) DueDate, JobProductID, JobID, ProductID, DepartmentID 
FROM      JobProducts AS JobProducts_1 
WHERE    JobProducts_1.JobID = J.JobID 
ORDER BY DueDate) 
AS derivedtbl_1

but I get the error The multi-part identifier "J.JobID" could not be bound.

但我得到了多部分标识符"J "的错误。JobID“不能被约束。

any help would be much appreciated

如有任何帮助，我们将不胜感激

2 个解决方案

#1

The error seems to be from SQL Server. I think that you are confusing CROSS JOIN (wich is a cartesian product) with CROSS APPLY:

错误似乎来自SQL Server。我认为你混淆了交叉连接(它是一个笛卡尔积)和交叉应用:

SELECT  J.CustomerID,  
        J.JobID, 
        J.Status, 
        J.Deleted, 
        J.JobNo, 
        Customers.CompanyName AS [Company], 
        J.DateCreated AS [Date Created], 
        derivedtbl_1.DueDate AS [Due Date] 
FROM Jobs  J
LEFT JOIN Customers 
    ON J.CustomerID = Customers.CustomerID 
CROSS APPLY (SELECT TOP (1) DueDate, 
                           JobProductID, 
                           JobID, 
                           ProductID, 
                           DepartmentID 
            FROM JobProducts AS JobProducts_1 
            WHERE JobProducts_1.JobID = J.JobID 
            ORDER BY DueDate) 
AS derivedtbl_1

#2

you may try changing the cross join to cross apply

您可以尝试将交叉连接更改为交叉应用

CROSS APPLY
(SELECT   TOP (1) DueDate, JobProductID, JobID, ProductID, DepartmentID 
FROM      JobProducts AS JobProducts_1 
WHERE    JobProducts_1.JobID = J.JobID 
ORDER BY DueDate)

#1