Querying the same column twice in a row

I'm having problems trying to select the student's name and also his professor's name in the same row.

TABLE USERS

ID --- NAME

TABLE RESEARCHES

STUDENT_ID (FK) --- PROFESSOR_ID (FK)
     referencing USERS

Like I said above, I want to select both names in the same row. Any suggestion?

In addition, there's an optional field in the table RESEARCHES called AUX_PROFESSOR, and if it's set, I'll need to select his name also. How can I do that?

Answers


You need multiple joins. The following will get the two names on one row:

select s.name as student_name, p.name as professor_name
from users s join
     researches r
     on s.id = r.student_id
     users p
     on p.id = r.professor_id;

If you then have one or two professors, you can do something like:

select s.name as student_name,
       max(p.name) as professor1_name,
       (case when min(p.name) <> max(p.name) then min(p.name) end) as professor2_name
     researches r
     on s.id = r.student_id
     users p
     on p.id = r.professor_id
group by s.name;

You don't say how to distinguish between the two types of professor in the data, so this just lists the two names.


You just need to alias the columns in the resultset.

SELECT  s.Name AS StudentName,
        p.Name AS ProfessorName
[...]

Need Your Help

New to asp.net. Need help debugging this email form

asp.net email

First of all, I am a php developer and most of .net is alien to me which is why I am posting here!

How to make this css code (counters and ;before) cross browser compatible using jquery?

jquery css

How to make this css code cross browser compatible using jquery. this code only works on firefox and IE8. i wan to use in IE6 and 7 also.