I am trying to pull the most recent completed job for my employees

Transact-SQL
1

SELECT [P-EmpList].LastName, [P-EmpList].FirstName,
ProjectCrewAllocation.SAPID, ProjectCrewAllocation.CompletedDate,
ProjectCrewAllocation.IntProj
FROM [P-EmpList]
INNER JOIN (
SELECT SAPID, MAX(CompletedDate) As 'Most Recent'
FROM ProjectCrewAllocation
GROUP BY SAPID
) AS ProjectCrewAllocation
ON [P-EmpList].SAPID = ProjectCrewAllocation.SAPID

**Error "The specified field 'ProjectCrewAllocation.Completed Date' could refer to more than one table listed in the FROM clause of your SQL statement.

I do not have this field in any other table?

2

On the line that reads "As ProjectCrewAllocation" you are defining a virtual table. Then there is the database table ProjectCrewAllocation. It is the conflict between these two that you are seeing in your query.

The recommended best practice is to assign short aliases to tables, and refer to columns by those aliases. For example like shown below. This may not be complete because I couldn't figure out where the column IntProj should come from.

SELECT
    pe.LastName,
    pe.FirstName,
    pca2.SAPID,
    pca2.CompletedDate
FROM
    [P-EmpList] AS pe
    INNER JOIN
    (
        SELECT
            pca.SAPID,
            MAX(pca.CompletedDate) AS CompletedDate
        FROM
            ProjectCrewAllocation AS pca
        GROUP BY
            pca.SAPID
    ) AS pca2 ON
        pe.SAPID = pca2.SAPID;

In the above, the line that reads ProjectCrewAllocation AS pca tells SQL Server that you are going to refer to the ProjectCrewAllocation table using the alias pca where that table is within scope in the query (in this case, the subquery).

1 Like