SQL db query using UNION and Left Join
Check:

WITH
TG1 AS ( SELECT PID AS ID, FVALUE, TVALUE
         FROM mydb.myuser.t1
         UNION 
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t2 
         UNION 
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t3 ),
TG2 AS ( SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T4
         UNION 
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T5
         UNION 
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T6 )
SELECT TG1.*, 
       TG21.PID FPID,
       TG22.PID TPID,
       TG21.VALUEID FVALUEID,
       TG22.VALUEID TVALUEID,
       TG21.VALUEDESC FVALUEDESC,
       TG22.VALUEDESC TVALUEDESC
FROM TG1
JOIN TG2 TG21 ON TG1.FVALUE = TG21.VALUE
JOIN TG2 TG22 ON TG1.TVALUE = TG22.VALUE