CODEWITHSUNDEEP
sqlt-sql
ElegantFellow
ElegantFellow

Reputation: 619

join on multiple columns

I have two tables (Table A and Table B) which I want to join on multiple columns in both tables.

Table A                         
Col1     Col2                
================            
A11      A21                 
A22      A22              
A33      A23                 

Table B 
Col1     Col2   Val 
=================  
B11     B21     1  
B12     B22     2  
B13     B23     3

I want both Columns in Table A to join on either of Col1 and Col2 in Table B to get Val.

Upvotes: 51

Views: 214146

Answers (4)

paparazzo
paparazzo

Reputation: 45096

Agree no matches in your example.
If you mean both columns on either then need a query like this or need to re-examine the data design.

    Select TableA.Col1, TableA.Col2, TableB.Val
    FROM TableA
    INNER JOIN TableB
          ON TableA.Col1 = TableB.Col1 OR TableA.Col2 = TableB.Col2 
          OR TableA.Col2 = TableB.Col1 OR TableA.Col1 = TableB.Col2

Upvotes: 65

gview
gview

Reputation: 15361

tableB.col1 = tableA.col1 
OR tableB.col2 = tableA.col1  
OR tableB.col1 = tableA.col2  
OR tableB.col1 = tableA.col2

Upvotes: 0

DRapp
DRapp

Reputation: 48139

The other queries are all going base on any ONE of the conditions qualifying and it will return a record... if you want to make sure the BOTH columns of table A are matched, you'll have to do something like...

select 
      tA.Col1,
      tA.Col2,
      tB.Val
   from
      TableA tA
         join TableB tB
            on  ( tA.Col1 = tB.Col1 OR tA.Col1 = tB.Col2 )
            AND ( tA.Col2 = tB.Col1 OR tA.Col2 = tB.Col2 )

Upvotes: 28

CharithJ
CharithJ

Reputation: 47530

Below is the structure of SQL that you may write. You can do multiple joins by using "AND" or "OR".

Select TableA.Col1, TableA.Col2, TableB.Val
FROM TableA, 
INNER JOIN TableB
 ON TableA.Col1 = TableB.Col1 OR TableA.Col2 = TableB.Col2

Upvotes: 3

Related Questions