Search code examples
sqljoinkey

SQL - Make join when multiple columns have the same key

I have a SQL table like:

claim  m1   m2   m3
1      0    20   100
2      0    25   105
...
1000   5    30   95

and a key table like:

M_Id M_Description
0   des0  
1   des1
2   des2
...
200 des200

M_Id is the key for m1, m2 and m3.

How can I make the join to get a table like:

claim   m1     m2      m3
1      des0    des20   des100
2      des0    des25   des105
...
1000   des5    des30   des95
Solution

  • You need three joins

    select t.claim, d1.M_Description  m1, d2.M_Description   m2, d3.M_Description  m3
from my_table t
join my_description d1 on (t.m1=d1.m_id)
join my_description d2 on (t.m2=d2.m_id)
join my_description d3 on (t.m3=d3.m_id)

    Every id column will be joined individually, and in result set you could use descriptions of corresponding join table with alias.