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.