hitbid hitbid - 5 months ago 9
SQL Question

Working on Adventureworks2014, not joining correctly

Using SqlServer 2014.

Below is my good and bad code. I am trying to query the HumanResources.Employee db, and include two things with it, the most recent pay upgrade in pay from the HumanResources.EmployeePayHistory in the form of "most recent date", and the First, Middle, and Last Names from the Person.Person file.

I was able to query the employee data and include the names successfully.

select b.*,(a.FirstName +' '+ isnull(a.MiddleName,'')+' '+a.LastName) as WHOLE_NAME
from Person.Person a
join HumanResources.Employee b
on a.BusinessEntityID = b.BusinessEntityID


I was able to then figure out how to get the correct date format out of the pay history file...

Select Convert(VarChar(10),ModifiedDate,101) as Date
From HumanResources.EmployeeDepartmentHistory


I know that I can find the MAX date for each pay upgrade like this:

Select BusinessEntityID, MAX(ModifiedDate) as MostRecent
From HumanResources.EmployeePayHistory
Group By BusinessEntityID


But when I try to combine three tables, my brain pretty much melts and I keep blowing it. Here is my mess:

Select Convert(VarChar(10),c.ModifiedDate,101) as Date, b.*,(a.FirstName +' '+ isnull(a.MiddleName,'')+' '+a.LastName) as WHOLE_NAME
From Person.Person a inner join
HumanResources.Employee b on a.BusinessEntityID = b.BusinessEntityID
inner join
(Select BusinessEntityID, MAX(ModifiedDate) as MostRecent
From HumanResources.EmployeePayHistory
Group By BusinessEntityID)
HumanResources.EmployeePayHistory c
on c.businessEntityID = a.BusinessEntityID


Can you help me fix this final three-table join attempt?
Many thanks.

Answer
  • Change this Convert(VarChar(10),c.ModifiedDate,101) as Date to this Convert(VarChar(10),c.MostRecent,101) as Date (since you have named the column you are referencing as MostRecent)
  • Change your alias on your derived table join to simply c

Whole query:

SELECT Convert(VarChar(10),c.MostRecent,101) as Date, 
       b.*,
      (a.FirstName +' '+ isnull(a.MiddleName,'')+' '+a.LastName) as WHOLE_NAME
FROM Person.Person a 
INNER JOIN HumanResources.Employee b on a.BusinessEntityID = b.BusinessEntityID
INNER JOIN (SELECT BusinessEntityID, MAX(ModifiedDate) as MostRecent
            FROM HumanResources.EmployeePayHistory
            GROUP BY BusinessEntityID
           ) c on c.businessEntityID = a.BusinessEntityID
Comments