Where does the sentence in the left join use a correlated query?

Question

Here's the request:

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 am trying to get the latest job status for a specific job. I can't figure out how to make a where clause (WHERE comment) in a LEFT JOIN. I did this in the past, but I don’t remember how I did it in the past.

I would be grateful for any pointers.

Answer 1

You need to use OUTER APPLY . The CROSS application is used as an INNER JOIN, where the application table should return results, while OUTER Apply is similar to [LEFT] OUTER JOIN, where the application subquery cannot return 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