Hi Folks,
I have a number of tables in a third party app which I am trying to
join in a view.

Some of the tables are similar to the following:

table jobtype
jobtype int
effectivedate datetime
personid

basically, the jobtype is applicable to the person from the effective
date.

the question is what is the best (most efficient) way of joining to
this table?

I have tried

select p.*, (select first 1 j.jobtype from jobtype j where j.personid
= p.personid and effectivedate <= current_timestamp order by
effectivedate desc) from person p

and

select p.*, (select j.jobtype from jobtype j where j.personid =
p.personid and effectivedate = (select max(effectivedate) from jobtype
j1 where j1.personid = p.personid and j1.effectivedate <=
current_timestamp)) from person p

Are there any other, cleaner ways to acheive the same result?

TIA
Ian