1

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.

  • additional fields from TG2 that match the values of TG1 specify matched field names pairs. – Akina Oct 21 at 13:56
  • The additional fields (FPID, TPID, FVALUEID, TVALUEID, FVALUEDESC and TVALUEDESC) are new fields populated from VALUE, VALUEID and VALUEDESC fieldsT of G2 that match the values of FVALUE and TVALUE fields from TG1. – Hab G. Oct 21 at 14:51
0

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
  • This is perfect. Thanks for your help Akina. – Hab G. Oct 21 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.