join on multiple columns

Question

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.

Answer 1

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 

Answer 2

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 ) 

Answer 3

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