Help with sql join

Question

I have two tables:

Table 1: ID, PersonCode, Name,

Table 2: ID, Table1ID, Location, ServiceDate

I've got a query joining table 1 to table 2 on table1.ID = table2.Table1ID where PersonCode = 'XYZ'

What I want to do is return Table1.PersonCode,Table1.Name, Table2.Location, Table2.ServiceDate, I don't want all rows, In table 2 I'm only interested in the row with the most recent ServiceDate for each location. How would I go about doing this?

Answer 1

Use MAX(ServiceDate)

Answer 2

Try:

select Table1.PersonCode,Table1.Name, Table2.Location, Table2.ServiceDate
from Table1
join Table2 on table1.ID = table2.Table1ID 
where table1.PersonCode = 'XYZ'
and table2.ServiceDate = (select max(t2.ServiceDate)
                          from   table2 t2
                          where  t2.table1ID = table2.table1ID
                          and    t2.location = table2.location
                         );

Answer 3

Something like this:

SELECT
    Table1.PersonCode, Table1.Name, Table2.Location, MAX(Table2.ServiceDate)
FROM
    Table1 
    INNER JOIN Table2 on Table1.ID = Table2.Table1ID 
WHERE
    TABLE1.PersonCode = 'XYZ'
GROUP BY
    Table1.PersonCode,Table1.Name, Table2.Location

Answer 4

I would use an INNER JOIN and select the first record, having ordered the records in reverse chronological order based on Table2.ServiceDate.

SELECT TOP 1
    Table1.PersonCode, Table1.Name, Table2.Location, Table2.ServiceDate
FROM
    Table1 
    INNER JOIN Table2 on Table1.ID = Table2.Table1ID 
WHERE
    TABLE1.PersonCode = 'XYZ'
ORDER BY Table2.ServiceDate DESC
GROUP BY
    Table1.PersonCode,Table1.Name, Table2.Location