CODEWITHSUNDEEP
mysqlsql
user1795928
user1795928

Reputation: 19

Join with 2 cols ids

Well I have following tables:

TableA

from_no  | to_no    | msg    
43288519 | 59215348 | hi    
43288519 | 123456   | hello    
59215348 | 43288519 | how are you.

TableB

contactno | Name    
43288519  | Priyam    
123456    | ADC   
59215348  | Ankur

Result I am looking for is:

from   | to     | msg    
Priyam | Ankur  | hi    
Priyam | adc    | hello   
Ankur  | Priyam | How are you

Please share the SQL Statement for the same.

Upvotes: 0

Views: 34

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Agreed with @Prdp.

One more way, Assuming contactno is unique, is:

select
    (select name from tableb where contactono = t.from_no),
    (select name from tableb where contactono = t.to_no),
    msg
from
    tablea t;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

You need to Join TableB twice 

select B1.Name as from, B2.Name as to, a.Msg 
from TableA A 
join TableB B1 on A.from_no  = B1.contactno 
join TableB B2 on A.to_no = B2.contactno

Considering there wont be any NULL values in from_no & to_no columns of TableA.

Upvotes: 3

Related Questions