I have a table VERSION with composite keys docId, verId:
docId verId apprvDt old_verId
------ ------ ----------- ----------
A 3 03/20/2017 2
A 2 03/18/2017 1
A 1 03/16/2017 null
B 1 03/18/2017 null
C 2 03/20/2017 1
C 1 03/16/2017 null
Say I select docId=A, verId=3, query should return
docId verId apprvDt old_verId old_apprvDt
------ ------ ----------- ---------- ------------
A 3 03/20/2017 2 03/18/2017
I tried:
select a.docId, a.verId, a.apprvDt, a.old_id, b.old_apprvDt
from VERSION as a left join (select x.docId, x.verId, x.apprvDt from REVISN as x where x.docId = 'A' and x.verId = a.old_verId) as b
on b.docId = a.docId and b.verId = a.old_verId
but I am getting a multi-part binding error.