How To Pull In Columns From A Derived Table Or Sub Query

Question

I have a query that looks for records that don't have a matching account number and tries to match those accounts by address.

I am getting the results I want, but I want to include columns from the table2 below. How can I do this?

Select DISTINCT
              account_num
        ,product
        ,accountName
        ,address_1
        ,address_2
        ,city
        ,state
        ,zip
        ,short_address
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS 
(SELECT * FROM (SELECT 
                       left(ADDRESS_LINE1_TXT,20) AS matching_add 
                      ,CITY
                      ,STATE
                      ,ZIP
                      ,ACCOUNT_OWNER
               From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
               WHERE 
               t.short_address= v.matching_add 
               AND t.city= v.NAME
               AND t.state = v.STATE
               AND t.zip = v.ZIP
               AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')

I've tried:

Select DISTINCT
              account_num
        ,product
        ,accountName
        ,address_1
        ,address_2
        ,city
        ,state
        ,zip
        ,short_address
              ,matching_add 
              ,CITY
        ,STATE
        ,ZIP
        ,ACCOUNT_OWNER
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS 
(SELECT * FROM (SELECT 
                       left(ADDRESS_LINE1_TXT,20) AS Select DISTINCT
              account_num
        ,product
        ,accountName
        ,address_1
        ,address_2
        ,city
        ,state
        ,zip
        ,short_address
INTO #Matching_Address
From #Non_Matching_Accounts t
Where EXISTS 
(SELECT * FROM (SELECT 
                       left(ADDRESS_LINE1_TXT,20) AS matching_add 
                      ,CITY
                      ,STATE
                      ,ZIP
                      ,ACCOUNT_OWNER
               From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
               WHERE 
               t.short_address= v.matching_add 
               AND t.city= v.NAME
               AND t.state = v.STATE
               AND t.zip = v.ZIP
               AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')
               From [database].[dbo].[table2]) v (matching_add, CITY, STATE,ZIP,ACCOUNT_OWNER)
               WHERE 
               t.short_address= v.matching_add 
               AND t.city= v.NAME
               AND t.state = v.STATE
               AND t.zip = v.ZIP
               AND t.accountName LIKE '%'+v.ACCOUNT_OWNER+'%')

Expected Results:

acct_num|prd|actName|add1|add2|city|state|zip|act_num2|prd2|actName|add1|add2|city2|state2|zip2|
----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
a   |  a  |  a |  a  |  a | a | a  | a   | a   | a   | a   | a  | a  | a    a|  a 
b   |  b  |  b |  b  |  b |  b  |  b |  b  |  b | b   |  b  |  b |  b  |  b |  b  
c   |  c  |  c |  c  |  c | c  |  c  |  c |  c  |  c | c  |  c |  c  |  c |  c |
d   |  d  |  d |  d  |  d |  d  |  d |  d  |  d |  d  |  d |  d  |  d | d |  d  |

Answer 1

You're using 'exists' when an 'inner join' is advised. Restructure as follows:

select 
distinct  t.account_num,
          t.product,
          t.accountName,
          t.address_1,
          t.address_2,
          t.city,
          t.state,
          t.zip,
          t.short_address,

          matching_add = left(v.address_line1_txt,20),
          vCity = v.city,
          vState = v.state,
          vZip = v.zip,
          v.account_owner

into      #Matching_Address
from      #Non_Matching_Accounts t
join      [database].[dbo].[table2] v
              on  t.short_address = v.matching_add 
              and t.city = v.name
              and t.state = v.state
              and t.zip = v.zip
              and t.accountName like '%' + v.account_owner + '%'

An inner join (or just 'join' for short), will only return matches, so it works like 'exists' in that sense. But it makes the columns from the right-hand table available to you.

My hunch is that you may have tried this. I see a 'distinct' in your query, which probably would not have been necessary with just 'exists'. Did you abandon 'inner join' because it was duplicating your rows? If so, 'exists' is still not the answer. Maybe a cross apply can help you:

select       ... (same as above)
into         #Matching_Address
from         #Non_Matching_Accounts t
cross apply  (
                select 
                top 1     *
                from      [database].[dbo].[table2] v
                where     t.short_address = v.matching_add 
                and       t.city = v.name
                and       t.state = v.state
                and       t.zip = v.zip
                and       t.accountName like '%' + v.account_owner + '%'
                order by  v.matching_add -- or whatever puts the better one on top
             ) v

With 'top 1', The 'v' result will produce no more than 1 record per row in 't'. With 'cross apply', if the result of 'v' is no records, then 't' will not return a row, (similar to 'exists' or 'inner join').