RE: Need ideas for SQL join select to replace logical view

From: Jeff Day

Bill,
I would think something like this should work:

select I_CONTROL#, I_STATUS, I_TMSP, O_OWNER
from inventory join owner on i_control# = o_control#
where O_TMSP <= I_TMSP

Unfortunately, this will result in multiple rows for each inventory record
because multiple owner records will be selected on the join (every owner
record with a timestamp less than or equal to the inventory timestamp). You
have to force the join to one and only one owner record, specifically the
one with the _highest_ timestamp less than or equal to the one on inventory.

It's not easy, but it can be done:

select I_CONTROL#, I_STATUS, I_TMSP, O_OWNER
from inventory join owner on I_CONTROL# = O_CONTROL#
where O_TMSP = (select MAX(O_TMSP) from owner
where O_CONTROL# = ICONTROL# and O_TMSP <= I_TMSP)

It may require some qualification of the files to eliminate ambiguities, but
the general idea is there.

Joe