How can I join 2 tables together and unpviot from both into one column?

Question

Here I have 2 queries:

SELECT bk, search, ckey, dn
FROM 
   (SELECT t1_bk_no AS 'bk', 
         t1_full_key AS 'ckey', 
         CAST(t1_info1 AS VARCHAR(100)) AS [1],
         CAST(t1_info2 AS VARCHAR(100)) AS [2],
         CAST(t1_info3 AS VARCHAR(100)) AS [3],
         CAST(t1_info4 AS VARCHAR(100)) AS [4],
         CAST(t1_info5 AS VARCHAR(100)) AS [5]
   FROM dbo.FirstTable) PNT
   UNPIVOT( searcg FOR dn IN ( [1],[2],[3],[4],[5] ) ) AS UPV
WHERE search NOT IN ( '0', '999999999', '') AND search IS NOT NULL

and

SELECT bk, search, ckey, dn
FROM 
   (SELECT t2_bk_no AS 'bk', 
         t2_full_key AS 'ckey', 
         CAST(t2_info1 AS VARCHAR(100)) AS [6],
         CAST(t2_info2 AS VARCHAR(100)) AS [7],
         CAST(t2_info3 AS VARCHAR(100)) AS [8]
   FROM dbo.SecondTable) PNT
   UNPIVOT( search FOR dnIN ( [1],[5],[7] ) ) AS UPV
WHERE search NOT IN ( '0', '999999999', '') AND search IS NOT NULL

Basically I want to join these 2 tables. Struggling to find where to put the join statement.

Any help appreciated, thanks.

Answer 1

Without seeing the desired result it seems as if you can use a UNION ALL query since the columns appear to be the same:

SELECT bk, search, ckey, dn
FROM 
(
  SELECT t1_bk_no AS 'bk', 
         t1_full_key AS 'ckey', 
         CAST(t1_info1 AS VARCHAR(100)) AS [1],
         CAST(t1_info2 AS VARCHAR(100)) AS [2],
         CAST(t1_info3 AS VARCHAR(100)) AS [3],
         CAST(t1_info4 AS VARCHAR(100)) AS [4],
         CAST(t1_info5 AS VARCHAR(100)) AS [5]
   FROM dbo.FirstTable
) PNT
UNPIVOT( search FOR dn IN ( [1],[2],[3],[4],[5] ) ) AS UPV
WHERE search NOT IN ( '0', '999999999', '') AND search IS NOT NULL
UNION ALL
SELECT bk, search, ckey, dn
FROM 
(
   SELECT t2_bk_no AS 'bk', 
         t2_full_key AS 'ckey', 
         CAST(t2_info1 AS VARCHAR(100)) AS [6],
         CAST(t2_info2 AS VARCHAR(100)) AS [7],
         CAST(t2_info3 AS VARCHAR(100)) AS [8]
   FROM dbo.SecondTable
) PNT
UNPIVOT( search FOR dnIN ( [1],[5],[7] ) ) AS UPV
WHERE search NOT IN ( '0', '999999999', '') AND search IS NOT NULL;