SQLTeam.com | Weblogs | Forums

Searching 2 columns in a Table with another table column


Hi - New here- I have 2 tables. I want to sequentially search the value of Table A column 2 to Table B column 1 and 2 in a full outer join. There will not be any cases where there is a match on both Table B column 1 and 32 for the same value from Table A.


Table A
Column 1 Column 2
Bob Jim
Jane Joe
Bill Fred

Table B
Column 1 Column 2
Jim Mary
Tim Tom
Don Fred

Desired Result

Table C (Full Outer Join)
Column 1 Column 2 Column 3 Column 4
Bob Jim Jim Mary
Jane Joe Null Null
Bill Fred Don Fred
Null Null Tim Tom


Show us what you have tried


/users/"Test-Data"/Sources/"Table_A"/"Table_A.xls"/"Export Worksheet" Export_Worksheet_A FULL OUTER JOIN
ON Export_Worksheet.Table_A_Column2 = Table_B_Column1
Export_Worksheet.Table_A_Column2 = Table_B_Column1 OR
Export_Worksheet.Table_A_Column2 = Table_B_Column2

So I want it to show 3 things:

  1. where Table_A_Column2 = Table_B_Column1
  2. where Table_A_Column2 = Table_B_Column2
  3. non matching

I have tried and googled other variants but get AND/WHERE errors so I posted here


Worksheets are normally not what I deal with, but maybe this will work for you:

select Table_A.Column1
  from openrowset('Microsoft.Jet.OLEDB.4.0'
                 ,'Excel 8.0;Database=\\users\\Test-Data\\Sources\\Table_A\\Table_A.xls;HDR=YES'
                 ,'select * from [Sheet1$]
                 ) as Table_A
       full outer join openrowset('Microsoft.Jet.OLEDB.4.0'
                                 ,'Excel 8.0;Database=\\users\\Test-Data\\Sources\\Table_B\\Table_B.xlsx;HDR=YES'
                                 ,'select * from [Sheet1$]
                                 ) as Table_B
                    on Table_B.Column1=Table_A.Column2
                    or Table_B.Column2=Table_A.Column2