SQL & PL/SQL :: Query To Find Usernames From Their IDs

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');

[Code]....

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.

a better way for this requirement.