# Thread: joining table on on last entered record

1. Registered User
Join Date
Mar 2005
Posts
92

## Unanswered: joining table on on last entered record

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?

2. Registered User
Join Date
Mar 2005
Posts
92
Maybe this:

select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...

this works faster but is there a better way? (I'm sure there is)

Anyone?

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I use this:
Code:
```select	A,
B
from	A
inner join --LastRecords
select	A,
max(date) as date
from	A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date```
...but I can't promise that it is faster.

4. Registered User
Join Date
Mar 2005
Location
Netherlands
Posts
280
Another possibility is:
Code:
```SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj```
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.

5. Registered User
Join Date
Mar 2005
Posts
92
Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
use a LEFT OUTER JOIN instead of INNER JOIN

7. Registered User
Join Date
Mar 2005
Posts
92
Thanks for that

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•