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.
ID --- NAME
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?
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 [...]