>>Table1.dbf >>id >>released_by >>prepared_by >>approved_by >>reviewed_by >>>>
>>users.dbf >>loginid >>name >>>>
>>select a.released_by, b.name as released_name, ; >> a.prepared_by, b.name as prepared_name, ; >> a.approved_by, b.name as approved_name, ; >> a.reviewed_by, b.name as reviewed_name ; >>From table1 a, users b >>>>
>select table1.released_by, Usr1.name as released_name, ; > table1.prepared_by, Usr2.name as prepared_name, ; > table1.approved_by, Usr3.name as approved_name, ; > table1.reviewed_by, Usr4.name as reviewed_name ; >From table1 >INNER JOIN users Usr1 ON table1.released_by = Usr1.Name >INNER JOIN users Usr2 ON table1.prepared_by = Usr2.Name >INNER JOIN users Usr3 ON table1.approved_by = Usr3.Name >INNER JOIN users Usr4 ON table1.reviewed_by = Usr4.Name >>This is of course if ALL *_by fields are full, if they are not use LEFT JOIN and NVL() function in result, like
>lcSpace = SPACE(LEN(Users.name)) >select table1.released_by, NVL(Usr1.name, m.lcSpace) as released_name, ; > table1.prepared_by, NVL(Usr2.name, m.lcSpace) as prepared_name, ; > table1.approved_by, NVL(Usr3.name, m.lcSpace) as approved_name, ; > table1.reviewed_by, NVL(Usr4.name, m.lcSpace) as reviewed_name ; >From table1 >LEFT JOIN users Usr1 ON table1.released_by = Usr1.Name >LEFT JOIN users Usr2 ON table1.prepared_by = Usr2.Name >LEFT JOIN users Usr3 ON table1.approved_by = Usr3.Name >LEFT JOIN users Usr4 ON table1.reviewed_by = Usr4.Name >