Hi,

First app, sorry for any silly oversights, but driven crazy by the sql query below.  I'm not seeing any syntax issues.  Any help is appreciated.  Thanks!

SyntaxEditor Code Snippet

SELECT {Personnel}.[Id], 
        {Department}.[Id], 
        {Department}.[CompanyId], 
        {Personnel}.[LastName], 
        {Personnel}.[FirstName], 
        {Company}.[CompanyCode], 
        {Department}.[DeptName], 
        {SalaryHistory}.[EffectiveDate], 
        {SalaryHistory}.[NewSalary]
        
FROM {Personnel}

LEFT JOIN 
(
SELECT {SalaryHistory}.[EffectiveDate], {SalaryHistory}.[NewSalary] FROM {SalaryHistory} 
    WHERE {SalaryHistory}.[EffectiveDate] = 
        (
        SELECT max(EffectiveDate) 
        FROM {SalaryHistory}
        WHERE {SalaryHistory}.[PersonnelId] = {Personnel}.[Id]
        )
) ON {Personnel}.[Id] = {SalaryHistory}.[PersonnelId]

LEFT JOIN 
(
SELECT {Department}.[Id], {Department}.[DeptName], {Department}.[CompanyId], {Company}.[CompanyCode] FROM {Department} 
    LEFT JOIN {Company} ON {Department}.[CompanyId] = {Company}.[Id]
) ON {Personnel}.[DepartmentId] = {Department}.[Id]

Executed SQL:

SyntaxEditor Code Snippet

SELECT  [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID],  
         [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID],  
         [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID],  
         [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[LASTNAME],  
         [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[FIRSTNAME],  
         [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[COMPANYCODE],  
         [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[DEPTNAME],  
         [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE],  
         [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[NEWSALARY] 
         
FROM  [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL] 
 
LEFT JOIN  
( 
SELECT  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE],  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[NEWSALARY] FROM  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY]  
    WHERE  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[EFFECTIVEDATE] =  
        ( 
        SELECT max(EffectiveDate)  
        FROM  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY] 
        WHERE  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[PERSONNELID] =  [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID] 
        ) 
) ON  [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[ID] =  [EUT4F5047].DBO.[OSUSR_WSH_SALARYHISTORY].[PERSONNELID] 
 
LEFT JOIN  
( 
SELECT  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID],  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[DEPTNAME],  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID],  [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[COMPANYCODE] FROM  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT]  
    LEFT JOIN  [EUT4F5047].DBO.[OSUSR_WSH_COMPANY] ON  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[COMPANYID] =  [EUT4F5047].DBO.[OSUSR_WSH_COMPANY].[ID] 
) ON  [EUT4F5047].DBO.[OSUSR_WSH_PERSONNEL].[DEPARTMENTID] =  [EUT4F5047].DBO.[OSUSR_WSH_DEPARTMENT].[ID]