使用关联查询在左连接中的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.

我正在努力获得一份工作的最新工作状态。我不知道如何执行左连接中的where子句(注释where)。我以前做过这个，但是不记得我以前是怎么做的。

I will be grateful for any pointers.

我将感激任何指示。

1 个解决方案

#1

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