title: How can I join 2 tables together and unpviot from both into one column?
body: <p>Here I have 2 queries:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Basically I want to join these 2 tables. Struggling to find where to put the join statement.</p>
  
  <p>Any help appreciated, thanks.</p>