SQLTeam.com | Weblogs | Forums

Searching 2 columns in a Table with another table column


#1

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.

Example

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


#2

Show us what you have tried


#3

FROM
/users/"Test-Data"/Sources/"Table_A"/"Table_A.xls"/"Export Worksheet" Export_Worksheet_A FULL OUTER JOIN
/users/c"Test-Data"/Sources/"Table_B"/"Table_B.xlsx"/Export_Worksheet_B
ON Export_Worksheet.Table_A_Column2 = Table_B_Column1
WHERE
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


#4

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

select Table_A.Column1
      ,Table_A.Column2
      ,Table_B.Column1
      ,Table_B.Column2
  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