CODEWITHSUNDEEP
sqloracle-database
user3186572
user3186572

Reputation: 1

oracle sql replace ID column with name

I have following two tables:

naming table

 M_ID     Name
----------------
 1      apple
 2      orange
 3      pear

mapping_table

 M_ID_1    M_ID_2
------------------
  1         2
  3         1
  2         3

How to make it output like that?????? I use oracle 11g.

 Name1     Name2
 ----------------
 apple      orange
 pear       apple
 orange     pear

Upvotes: 0

Views: 1488

Answers (4)

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

Try this query:

SELECT (SELECT n.name 
        FROM   name_table n 
        WHERE  m.m_id_1 = n.m_id) name1, 
       (SELECT n.name 
        FROM   name_table n 
        WHERE  m.m_id_2 = n.m_id) name2 
FROM   mapping_table m;

Upvotes: 1

San
San

Reputation: 4538

Query 

with tab(M_ID, Name) as
(select 1,'apple' from dual union all
 select 2,'orange' from dual union all
 select 3,'pear' from dual),
tab2(M_ID_1, M_ID_2) as
(select 1,2 from dual union all 
 select 3,1 from dual union all 
 select 2,3 from dual)
----
--End of data
----
select (select name from tab where m_id = M_ID_1) M_ID_1, 
       (select name from tab where m_id = M_ID_2) M_ID_2
  from tab2;

Output:

| M_ID_1 | M_ID_2 |
|--------|--------|
|  apple | orange |
|   pear |  apple |
| orange |   pear |

Upvotes: 0

Dba
Dba

Reputation: 6639

Try like this,

SELECT a.name name1, b.name name2
FROM   naming_table A,
       naming_table B,
       mapping_table c
WHERE  A.m_id = c.m_id_1
AND    b.m_id = c.m_id_2;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Something like this would seem to be what you're after

SELECT n1.name name1,
       n2.name name2
  FROM mapping_table m
       JOIN name_table n1
         ON( m.m_id_1 = n1.m_id )
       JOIN name_table n2
         ON( n.m_id_2 = n2.m_id )

Upvotes: 1

Related Questions