What will be oracle query for below problem?-CodePudding

I have two tables

Table_1

Sr_no  Item_1_id       Item_2_id
---------------------------------
1       3                 2 
2       2                 1
3       1                 3

Table_2

Item_id    Item_Name
-------------------------
1          Key Board
2          Monitor
3          CPU

I Need output like below

Sr_no  Item_1_Name       Item_2_Name
---------------------------------
1       CPU                 Monitor 
2       Monitor             Key Board
3       Keyboard            CPU

What will be the oracle query for this output?

CodePudding user response：

Query -

select t1.Sr_no,
case when t1.Item_1_id = t2.Item_id then t2.Item_Name end item_1_name,
case when t1.Item_1_id = t3.Item_id then t3.Item_Name end item_2_name
from Table_1 t1 left join Table_2 t2
on t1.Item_1_id = t2.item_id
left join Table_2 t3
on t1.Item_1_id = t3.item_id

Fiddle here with few other approaches.

CodePudding user response：

You need to join Table_2 twice, as in:

select a.sr_no, b.item_name, c.item_name
from table_1 a
join table_2 b on b.item_id = a.item_1_id
join table_3 c on c.item_id = a.item_2_id