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

IMO the problem is the database design. Historical ownership can not be correctly represented with only a transaction time. Such historical ownership would be a duration. The ownership table also fails to denote that the *STORE is the owner between transactions which would complete known ownership history.
I would define the OWNER table to have a column for both a FROM_TMSP and UNTIL_TMSP to designate the duration that the item is owned; the end of the duration is CURRENT TIMESTAMP when not defined. Having done so, the problem of designating the OWNER for each sequential [historical] timestamped event should be more easily grasped, as ordered results of a LEFT OUTER JOIN WHERE EVENT_TMSP BETWEEN FROM_TMSP AND UNTIL_TMSP The range for selection is defined in a row, rather than between that and a prior row.

SELECT i.*, o.owner
FROM INVENTORY I
LEFT OUTER JOIN OWNER O
WHERE i.tmsp BETWEEN o.from_tmsp
AND ifnull(o.until_tmsp, current timestamp)
AND o.owner <> '*STORE'

An insert trigger on the OWNER file can enforce the rule that both the previous historical until-time and the new historical from-time will get the same timestamp value, and that the until-time of the new row gets the NULL value since the until-time for the current owner is indeterminate until the next insert defining the start of the next duration of ownership.

Regards, Chuck