Join three sources without common fields and assign one fields to other field

[Mounika1025]

I have three sources on same index ="test"

source1 source2 RefSource2

Trans_ID SourceType TRANS_ID Trans_Name Trans_Type REF_ID REF_DESC
123 01 123 Name1 03 01 Type1
234 02 345 Name4 04 02 Type2
345 03 567 Name2 02 03 Type3
04 Type4

Result table should be

Trans_ID SourceType Trans_Name Trans_Type
123 Type1 Name1 Type3

345 Type3 Name4 Type4 where Trans_id is common for source1and source2..

Please help me how to achive above result

[Mounika1025]

Attached image ralted to tables and its relation .                      
Source 1 &source2 has common filed Trans_ID                     
IF Source1.SourceType=REF_ID then assign Source1.SourceType=REF_DESC                        
IF Source2.Trans_Type=REF_ID then assign Source2.Trans_Type=REF_DESC                        

                        ![alt text][1]
Final Result should be:                     

Trans_ID     SourceType Trans_Name  Trans_Type          
123 Type1   Name1   Type4           
345 Type3   Name3   Type2           

[somesoni2]

Can you post which source have what fields and (if any) relationship between those fields?