How do I do a join and exclude certain records?

Question

Table STUDENT has columns ID, LASTNAME, FIRSTNAME

Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID

Students can have many teachers. Teachers can add students to their class.

I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.

What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.

SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID  ??? 

This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?

I'm coding in PHP and using DQL, but any help on just the query part would be great.

Hope that makes sense. I confuse myself.

Answer 1

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.

This is TSQL so it may be wrong for mysql

SELECT S.ID
     , S.LASTNAME
     , S.FIRSTNAME
  FROM STUDENTS S
  LEFT JOIN TEACHERS T 
        ON T.STUDENT_ID = S.ID
       AND T.TEACHER_ID = $teacherID 
 WHERE T.TEACHER_ID is null

Answer 2

Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.

See if the below works for you.

SELECT S.ID
    , S.LASTNAME
    , S.FIRSTNAME
FROM STUDENTS S
    LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID

Answer 3

OK let me guess that TEACHER table is what gets an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID

SELECT S.ID
     , S.LASTNAME
     , S.FIRSTNAME
  FROM STUDENTS S
HAVING S.ID NOT IN 
  (SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )