CODEWITHSUNDEEP
sqlt-sqlsql-server-2008correlated-subquery
Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

Where clause in left join using correlated query?

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.

Upvotes: 3

Views: 2392

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

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

Upvotes: 5

Related Questions