retrieving information from 2 tables

[gunjangogia]

Hi everyone,

Could someone please help me out. I am having problems retrieving information from a table where 2 attributes are foreign keys of the SAME attribute in another table....

The tables are
USER(id, name, type_login)
STUDENT(student_id, mentor_id)

here, student_id and mentor_id are foreign keys of the USER table....
I need to get a list of all the students with their respective mentors....
I think the problem is because we are referencing the same coloumn of the user table....

Looking forward to a reply soon!
Thanks & Regards
Gunjan

[bhuvanesh1]

Hi,

You can write the qry in inline view to find out the details,

Here is the sample,

SELECT
S.student_id,
S.mentor_id,
(SELECT name FROM USER WHERE id=S.student_id ) as STUDENT_NAME,
(SELECT type_login FROM USER WHERE id=S.student_id ) as STUDENT_TYPE_LO GIN,
(SELECT name FROM USER WHERE id=S.mentor_id) as MENTOR_NAME,
(SELECT type_login FROM USER WHERE id=S.mentor_id) as MENTOR_TYPE_LOG IN
FROM
STUDENT S


Thanks
Bhuvanesh