Help with sql join

2
votes

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?

4
sqlsql-server

4 Answers

3
votes

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
0
votes

Use MAX(ServiceDate)

0
votes

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
                         );
0
votes

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