SQL db query using UNION and Left Join

queryunion

I have 6 tables (t1, t2, t3, …,t6 in the same SQL Server 2008 R2 database. I wanted to group them into two. Group 1 (TG1) includes tables 1, 2 and 3. Group 1 (TG1) includes tables 4, 5 and six.
I have created two queries each with union

Example Image

SELECT TG1.*
  FROM (SELECT t1.PID AS ID, t1.FVALUE AS FVALUE, t1.TVALUE AS TVALUE
         FROM mydb.myuser.t1
         UNION 
         SELECT t2.PID AS ID, t2.FVALUE AS FVALUE, t2.TVALUE AS TVALUE
         FROM mydb.myuser.t2 
         UNION 
         SELECT t3.PID AS ID, t3.FVALUE AS FVALUE, t3.TVALUE AS TVALUE
         FROM mydb.myuser.t3) AS TG1 
SELECT TG2.*
  FROM (SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4) AS TG2

I am getting the following two tables

enter image description here

The final query that I want to create is as shown in the screenshot below by taking all the values of TG1 and populate additional fields from TG2 that match the values of TG1.

enter image description here

The values in above screenshot were populated in Excel using vlookup and I need help to do the same in SQL query. I appreciate your help.

Best Answer

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
Related Question