Thanks for the feedback Biraja. So, if I am understanding you correctly your are suggesting something like this:
with lina as (select a.objectid, s.lineage_name, s.state_id from a293 a
join sde.states s on s.state_id = a.sde_state_id),
linver as (select s.state_id, s.lineage_name, v.name from sde.versions v
join sde.states s on v.state_id = s.state_id)
select * from lina
full outer join linver on lina.lineage_name = linver.lineage_name
But what about the rows in the "A" table that do not join to a current version lineage? How do I understand what version they were part of?