使用相关查询的左连接中的 where 子句？

发布于 2024-10-16 21:52:16 字数 861 浏览 4 评论 0原文

这是查询：

SELECT c.Name As CompanyName, j.ID as JobID, j.Title as JobTitle, 
ja.ApplicationDate, DATEDIFF(MONTH,ja.ApplicationDate, GETDATE()) AS MonthsAgo,
jsc.Name As Recruiter, js.Name As RecruitingAgency, jsh.Name As LastStatus
FROM Companies c
JOIN Jobs j 
ON c.ID = j.CompanyID
JOIN JobApplications ja
ON j.ID = ja.JobID
LEFT JOIN JobContact jsc
ON jsc.ID = j.JobSourceContactID
LEFT JOIN JobContactCompany js
ON jsc.JobSourceCompanyID = js.ID
LEFT JOIN (
    SELECT TOP 1 jh.JobID, jh.StatusDate, jt.Name
    FROM JobStatusHistory jh
    JOIN JobStatusTypes jt
    ON jh.JobStatusTypeID = jt.ID
    --WHERE jh.JobID = j.ID
    ORDER BY jh.StatusDate DESC
    ) jsh
ON j.ID = jsh.JobID
ORDER BY ja.ApplicationDate

我正在尝试获取特定工作的最新工作状态。我不知道如何在 LEFT JOIN 中执行 where 子句（带注释的 WHERE）。我过去曾这样做过，但不记得过去是如何做到的。

我将不胜感激任何指点。

原文

Here's the query:

SELECT c.Name As CompanyName, j.ID as JobID, j.Title as JobTitle, 
ja.ApplicationDate, DATEDIFF(MONTH,ja.ApplicationDate, GETDATE()) AS MonthsAgo,
jsc.Name As Recruiter, js.Name As RecruitingAgency, jsh.Name As LastStatus
FROM Companies c
JOIN Jobs j 
ON c.ID = j.CompanyID
JOIN JobApplications ja
ON j.ID = ja.JobID
LEFT JOIN JobContact jsc
ON jsc.ID = j.JobSourceContactID
LEFT JOIN JobContactCompany js
ON jsc.JobSourceCompanyID = js.ID
LEFT JOIN (
    SELECT TOP 1 jh.JobID, jh.StatusDate, jt.Name
    FROM JobStatusHistory jh
    JOIN JobStatusTypes jt
    ON jh.JobStatusTypeID = jt.ID
    --WHERE jh.JobID = j.ID
    ORDER BY jh.StatusDate DESC
    ) jsh
ON j.ID = jsh.JobID
ORDER BY ja.ApplicationDate

I'm trying to get the most recent job status for a particular job. I can't figure out how to do the where clause (the commented WHERE) in the LEFT JOIN. I've done this in the past, but can't remember how I did this in the past.

I will be grateful for any pointers.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听，心雨的声音 2024-10-23 21:52:16

您需要使用外部应用。 CROSS Apply 类似于 INNER JOIN，其中应用的表必须返回结果，而 OUTER Apply 类似于 [LEFT] OUTER JOIN，其中应用的子查询可能不返回结果。

SELECT c.Name As CompanyName, j.ID as JobID, j.Title as JobTitle, 
    ja.ApplicationDate, DATEDIFF(MONTH,ja.ApplicationDate, GETDATE()) AS MonthsAgo,
    jsc.Name As Recruiter, js.Name As RecruitingAgency, jsh.Name As LastStatus
FROM Companies c
JOIN Jobs j ON c.ID = j.CompanyID
JOIN JobApplications ja ON j.ID = ja.JobID
LEFT JOIN JobContact jsc ON jsc.ID = j.JobSourceContactID
LEFT JOIN JobContactCompany js ON jsc.JobSourceCompanyID = js.ID
OUTER APPLY ( 
    SELECT TOP 1 jh.JobID, jh.StatusDate, jt.Name
    FROM JobStatusHistory jh
    JOIN JobStatusTypes jt
    ON jh.JobStatusTypeID = jt.ID
    WHERE jh.JobID = j.ID
    ORDER BY jh.StatusDate DESC
    ) jsh
ORDER BY ja.ApplicationDate

You need to use OUTER APPLY. A CROSS Apply is like an INNER JOIN where the applied table must return results, whereas an OUTER Apply is like a [LEFT] OUTER JOIN where the applied subquery may return no results.

SELECT c.Name As CompanyName, j.ID as JobID, j.Title as JobTitle, 
    ja.ApplicationDate, DATEDIFF(MONTH,ja.ApplicationDate, GETDATE()) AS MonthsAgo,
    jsc.Name As Recruiter, js.Name As RecruitingAgency, jsh.Name As LastStatus
FROM Companies c
JOIN Jobs j ON c.ID = j.CompanyID
JOIN JobApplications ja ON j.ID = ja.JobID
LEFT JOIN JobContact jsc ON jsc.ID = j.JobSourceContactID
LEFT JOIN JobContactCompany js ON jsc.JobSourceCompanyID = js.ID
OUTER APPLY ( 
    SELECT TOP 1 jh.JobID, jh.StatusDate, jt.Name
    FROM JobStatusHistory jh
    JOIN JobStatusTypes jt
    ON jh.JobStatusTypeID = jt.ID
    WHERE jh.JobID = j.ID
    ORDER BY jh.StatusDate DESC
    ) jsh
ORDER BY ja.ApplicationDate

回复收藏 0 原文

~没有更多了~