CODEWITHSUNDEEP
sqlsql-server
Jeremy
Jeremy

Reputation: 46440

Help with sql join

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?

Upvotes: 2

Views: 174

Answers (4)

DJ.
DJ.

Reputation: 16267

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

Upvotes: 3

Julius A
Julius A

Reputation: 39672

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

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132750

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

Upvotes: 0

dove
dove

Reputation: 20692

Use MAX(ServiceDate)

Upvotes: 0

Related Questions