example code :
select top 1 name from DIRPARTYTABLE inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID AND HcmWorker.RECID = (select top 1 worker from HCMPOSITIONWORKERASSIGNMENT INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION = HCMPOSITION.RECID AND HCMPOSITION.POSITIONID = 'P0003' where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);