Join on TOP 1 from subquery while referencing outer tables

I am starting with this query, which works fine:

SELECT 
    C.ContactSys
    , ... a bunch of other rows... 
FROM Users U
    INNER JOIN Contacts C ON U.ContactSys = C.ContactSys 
    LEFT JOIN UserWatchList UW ON U.UserSys = UW.UserSys 
    LEFT JOIN Accounts A ON C.AccountSys = A.AccountSys 
WHERE 
     C.OrganizationSys = 1012
     AND U.UserTypeSys = 2 
     AND C.FirstName = 'steve'

Now, I’ve been given this requirement:

For every visitor returned by the Visitor Search, take ContactSys, get the most recent entry in the GuestLog table for that contact, then return the columns ABC and XYZ from the GuestLog table.

I’m having trouble with that. I need something like this (I think)…

SELECT 
    C.ContactSys
    , GL.ABC
    , GL.XYZ
    , ... a bunch of other rows... 
FROM Users U
    INNER JOIN Contacts C ON U.ContactSys = C.ContactSys 
    LEFT JOIN UserWatchList UW ON U.UserSys = UW.UserSys 
    LEFT JOIN Accounts A ON C.AccountSys = A.AccountSys 
    LEFT JOIN (SELECT TOP 1 * FROM GuestLog GU WHERE GU.ContactSys = ????? ORDER BY GuestLogSys DESC) GL ON GL.ContactSys = C.ContactSys
WHERE 
     C.OrganizationSys = 1012
     AND U.UserTypeSys = 2 
     AND C.FirstName = 'steve'

Only that’s not it because that subquery on the JOIN doesn’t know anything about the outer tables.

I’ve been looking at these posts and their answers, but I’m having a hard time translating them to my needs:

SQL: Turn a subquery into a join: How to refer to outside table in nested join where clause?

Reference to outer query in subquery JOIN

Referencing outer query in subquery

Referencing outer query’s tables in a subquery

Answer

If that is the logic you want, you can use OUTER APPLY:

SELECT C.ContactSys, GL.ABC, GL.XYZ,
        ... a bunch of other columns ... 
FROM Users U JOIN
     Contacts C
     ON U.ContactSys = C.ContactSys LEFT JOIN
     UserWatchList UW 
     ON U.UserSys = UW.UserSys LEFT JOIN
     Accounts A
     ON C.AccountSys = A.AccountSys OUTER APPLY
     (SELECT TOP 1 gl.*
      FROM GuestLog gl
      WHERE gl.ContactSys = C.ContactSys
      ORDER BY gl.GuestLogSys DESC
     ) GL
WHERE C.OrganizationSys = 1012 AND
      U.UserTypeSys = 2 AND
      C.FirstName = 'steve'

Advertisement

Answer