Question 21206 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use a mapping table in sql? – Mysql

I have the 2 following table :

Source table with 2 columns (large number of records) :

columnA      columnB
abcdez       defghp
azerty       ghjklm
...
...

Mapping table with source value and new value. All columnA and columnB values in the source table have a matching value as source_value in the mapping table, with a corresponding new_value.

source_value      new_value
abcdez            123456
defghp            999999
azerty            444444
ghjklm            101010
...
...
...

I would like to use sql to build a view of my source table, with their replacement values from my mapping table :

View1 :

columnA       columnB
123456        999999
444444        101010
...
...

Thanks ahead for your help

Tags:

2

Answers


  1. 0

    You need to join to the mapping table twice.

    If you think of a join as a correlation between specific rows of each table, this makes a bit more sense.

    SELECT m1.new_value AS columnA, m2.new_value AS columnB
FROM SourceTable AS s
JOIN MappingTable AS m1 ON s.columnA = m1.source_value
JOIN MappingTable AS m2 ON s.columnB = m2.source_value;
    Login or Signup to reply.
  2. 0 
    select t2.new_value as colA,t3.new_value as colb
from test1 t1 inner join test2 t2 on 
t1.cola=t2.source_value
inner join test2 t3 on 
t1.colb=t3.source_value

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search