Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve text for ID from another table for two columns in a SELECT statement (Oracle 11.2.0.3.0 , OEL 5)
Retrieve text for ID from another table for two columns in a SELECT statement [message #654062] Tue, 26 July 2016 09:58 Go to next message
Andrey_R
Messages: 372
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 Go to previous messageGo to next message
John Watson
Messages: 8592
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 Go to previous messageGo to next message
Andrey_R
Messages: 372
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Tue, 26 July 2016 18:16
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.



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;

Re: Retrieve text for ID from another table for two columns in a SELECT statement [message #654065 is a reply to message #654064] Tue, 26 July 2016 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes and don't forget the primary and foreign keys to help the optimizer.

Re: Retrieve text for ID from another table for two columns in a SELECT statement [message #654066 is a reply to message #654064] Tue, 26 July 2016 10:35 Go to previous message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
Sussed! Except that I would use ANSI join syntax.

Speaking as a database administrator, your first try was an example of why I hate developers who don't talk to their DBA. It did what you want, but in a horribly inefficient way. So many developers have no idea of the impact their code has on the database, and refuse to discuss this. In my experience, Java programmers are the worst for this.

[Updated on: Tue, 26 July 2016 10:36]

Report message to a moderator

Previous Topic: How can I load 300k of data into a CLOB
Next Topic: XML tag selection
Goto Forum:
  


Current Time: Fri Sep 17 10:27:18 CDT 2021