var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: joining table on on last entered record
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:
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 ...)
select A, B
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)
I use this:
...but I can't promise that it is faster.
inner join --LastRecords
max(date) as date
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
Another possibility is:
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.
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
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?
use a LEFT OUTER JOIN instead of INNER JOIN