How can I fix ambiguous SQL Column reference when using a subquery and alias is already being used for tables/columns with none left ambigious?

Question

This is my query, but I'm getting the error:

ERROR: Column reference "TUTOR_ID" is ambigious"

SELECT 
   s1.STUDENT_NAME, 
   d.TUTOR_NAME,
   d.ROOM_NUMBER 
FROM 
   STUDENTS s1
JOIN
   (
        SELECT * FROM STUDENTS s2 
        JOIN TUTORS t
        ON 
          s2.TUTOR_ID = t.TUTOR_ID
        JOIN ROOMS r
        ON
           t.ROOM_ID = r.ROOM_ID
           AND r.ROOM_STATUS = 'ACTIVE'
        ORDER BY
           t.NEXT_AVAILABLE_TIME
        LIMIT 1
   ) d
ON
  s1.TUTOR_ID = d.TUTOR_ID

What are the mechanics behind the ambiguous error? I don't understand how I'm getting it when every table has an alias and the columns are all using the alias' of their table when referenced.

The goal of this query is to essentially limit the resulting records so that it only returns one combination of student/tutor per room. So if there are 10 students, and 10 tutors, and 2 rooms, it will return only the two records for students who have a tutor with those room_ids.The query needs to start from students.

Answer 1

This is caused because the joins within the subquery result in multiple TUTOR_IDs existing within the result set returned by select *. This is ambigious, and the subquery needs to know which TUTOR_ID (and other duplicate columns) to return as the result set for subquery d.

This can be resolved by changing the select all:

SELECT * FROM STUDENTS s2 

to directly specify the columns wanted from the subquery:

SELECT t.TUTOR_ID, t.TUTOR_NAME, r.ROOM_NUMBER FROM STUDENTS s2