Retrieve text for ID from another table for two columns in a SELECT statement [message #654062] |
Tue, 26 July 2016 09:58 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I have tables as follows:
create table orders (order_id number(6),creator_user_id number(5) , modifier_user_id number(5) );
create table system_users (user_id number(5),user_name varchar2(20));
insert into orders (order_id , creator_user_id , modifier_user_id ) values (1001, 210 , 220);
insert into orders (order_id , creator_user_id , modifier_user_id ) values (1002, 205 , 205);
insert into system_users (user_id, user_name ) values (205 , 'John');
insert into system_users (user_id, user_name ) values (210 , 'Tim');
insert into system_users (user_id, user_name ) values (220 , 'Laura');
commit;
I want to conduct a query to retrieve order_id , the creator user *name* and modifier user names, translated by user_id from system_users table.
It will look like:
order_id creator modifier
-------- ------- ---------
1001 Tim Laura
1002 John John
I can achieve it by
Select order_id ,
(select user_name from system_users where user_id = orders.creator_user_id ) as creator,
(select user_name from system_users where user_id = orders.modifier_user_id ) as modifier
from orders ;
Is there a way to do it without a double access to system_users table ?
Regards,
Andrey
|
|
|
Re: Retrieve text for ID from another table for two columns in a SELECT statement [message #654063 is a reply to message #654062] |
Tue, 26 July 2016 10:16 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Actually, you are not hitting system_users twice, you are hitting it four times: twice for each row in orders. Think of the effect when you have millions of orders.... That is the problem with projecting a subquery: it has to be correlated.
If you re-write it so that orders joins to system_users twice (once on creator_user_id and once on modifier_user_id) then you will hit system_users only twice.
|
|
|
Re: Retrieve text for ID from another table for two columns in a SELECT statement [message #654064 is a reply to message #654063] |
Tue, 26 July 2016 10:25 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
John Watson wrote on Tue, 26 July 2016 18:16Actually, you are not hitting system_users twice, you are hitting it four times: twice for each row in orders. Think of the effect when you have millions of orders.... That is the problem with projecting a subquery: it has to be correlated.
If you re-write it so that orders joins to system_users twice (once on creator_user_id and once on modifier_user_id) then you will hit system_users only twice.
like this?
Select o.order_id ,
s_cre.user_name as creator,
s_mod.user_name as modifier
from orders o , system_users s_cre , system_users s_mod
where o.creator_user_id = s_cre.user_id
and o.modifier_user_id = s_mod.user_id;
|
|
|
|
|