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
5
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
0
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
5
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
0
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)