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?

DJ. · Accepted Answer · 2008-10-31 18:50:13Z

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

edited Oct 31, 2008 at 18:50

answered Oct 31, 2008 at 16:50

DJ.

16.3k3 gold badges45 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Julius A Over a year ago

Why LEFT JOIN? I thought INNER JOIN is suffient? I would modify your query - Please see my answer below

DJ. Over a year ago

Sorry I was working on another question with left joins - edited

dove · Accepted Answer · 2008-10-31 16:46:51Z

0

Use MAX(ServiceDate)

answered Oct 31, 2008 at 16:46

dove

20.8k14 gold badges88 silver badges109 bronze badges

Comments

Tony Andrews · Accepted Answer · 2008-10-31 16:49:46Z

0

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

answered Oct 31, 2008 at 16:49

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Comments

Julius A · Accepted Answer · 2008-10-31 17:02:49Z

0

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

answered Oct 31, 2008 at 17:02

Julius A

40k27 gold badges79 silver badges98 bronze badges

Collectives™ on Stack Overflow

Help with sql join

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related