Hey guys... I'm having problems with writing a SQL statement that I really shouldn't be.
I'm trying to fill a <select> box with values in a SQL statement. It is working no problem at all, but I need to limit what is in the box.
It is for an online gradebook. I'm filling in a box to assign students into a class and store them in a ClassRoster table. I need to limit the <select> box to students who are not already in the Class I have selected. I'm assuming that the SQL here works like it does in Access, but here is what I have. Sorry for the variable names...
If I fill the box with this query, it fills in the box with students that are in the class. It is correct there.
$CRsql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S, tblClassRoster as CR WHERE S.Student_ID = CR.Student_ID AND CR.Class_ID = '$Class_ID_E' and CR.Class_Section_No = '$Class_Section_No_E' AND CR.Class_Semester = '$Class_Semester_E' AND CR.Class_Year = '$Class_Year_E' ORDER BY S.Student_LN";
$CurrRoster=mysql_query($CRsql) or die(mysql_error());
and then:
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S, CurrRoster as CR where S.Student_ID <> CR.Student_ID AND CR.Class_ID = '$Class_ID_E' and CR.Class_Section_No = '$Class_Section_No_E' AND CR.Class_Semester = '$Class_Semester_E' AND CR.Class_Year = '$Class_Year_E' ORDER BY S.Student_LN";
I'm not sure about comparing query to query in the above. No matter what I do, I get an empty <select> box. I've tried left joins as well but my knowledge of left joins has faded in the past year or so.
Any help would be great and appreciated!! Thanks
