Home » SQL & PL/SQL » SQL & PL/SQL » Query to find usernames from their Ids (Oracle 11.2.0.2.0)
Query to find usernames from their Ids [message #569219] Tue, 23 October 2012 01:42 Go to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi,
Could you please help me with the below requirement.
I have 2 tables, first one MAIN_T, which contains, user_id and username.
The second table is a relationship table which contains the referal details.
The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.
-- Test Case
CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);

INSERT INTO main_t  VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');

INSERT INTO  relation_t VALUES(1,2);
INSERT INTO  relation_t VALUES(2,3);
INSERT INTO  relation_t VALUES(1,5);
INSERT INTO  relation_t VALUES(5,4);

I have written this query, which gives me the expected output.
SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id  
AND rltn.frnd_id=frnd.user_id
/
MEMBER_NAM FRIEND_NAM
---------- ----------
  u1         u2
  u2         u3
  u5         u4
  u1         u5

4 rows selected.

Could you please suggest me a better way for this requirement.
Thank you,
Re: Query to find usernames from their Ids [message #569220 is a reply to message #569219] Tue, 23 October 2012 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59166
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no better way (in the real world with pk, indexes...).

Regards
Michel
Re: Query to find usernames from their Ids [message #569221 is a reply to message #569220] Tue, 23 October 2012 01:51 Go to previous message
yuko
Messages: 65
Registered: August 2011
Member
Michel,
Thank you for the reply.
Previous Topic: Need to get value from one column but display id different fields.
Next Topic: Newbie SELECT WHERE DATE
Goto Forum:
  


Current Time: Mon Sep 22 19:57:10 CDT 2014

Total time taken to generate the page: 0.17676 seconds