SQL call the same table twice in one query?

Question

I have a table with studentID and advisorID along with another table with IDnum, Lname and Fname. I need a query that will list the names of the student with their advisor.

For instance:

StudentID: AdvisorID:

11112 98989

11113 67676

11114 98989

IDNum: Lname: Fname:

11112 Jon Dow

11113 Jane Dow

11114 Tom Smith

98989 Jill Smith

67676 Hank Jones

Should appear like:

Jon Dow Jill Smith

Jane Down Hank Jones

Tom Smith Jill Smith

Answer 1

I don't think this can be done, because you've got no way to specify which is a student and which is an advisor.

Answer 2

I don't know what database you are using? If that is Oracle, I think you can write a function as a solution.

SELECT get_fname(StudentID), get_lname(StudentID), get_fname(AdvisorID), get_lname(AdvisorID) from Students;

You need make two functions get_fname() and get_lname().

Good luck,

peng

http://www.eptop.com

Answer 3

This is a totally incorrect use of a database. You need to use tables for students, then one for advisors. If the advisors are also students you could just have the id of each student who is also an advisor in the extra table. You are better using one sql statement to select the students iterating through the results, then another query selecting the advisors. This is better done at program level and simplifies the sql. It also reduces load on the server under heavy usage.

Answer 4

you can access the same table twice but you need to give them aliases so the where clause (and select clause) know which table you're referring to

(aliases are "s" and "a" in my example below)

something like this...

select s.name, a.name from studentadvisroxref x,

persontable s, persontable a

where x.advisroid = a.idnum

and x.studentid = s.idnum

although you SHOULD segregate the student records from the advisor records into 2 tables....i took the assumption that you're stuck with a poorly designed database and you need to work with it.