Hey guys. I posted this a week or two ago and got some responses, and have tried all of them, but I'm still not doing something right apparently. Here's what I'm going... I'm creating an online gradebook and I want to add students into a class and put that info in the ClassRoster table. I'm trying to fill a SELECT box with every student except those already enrolled in the class. Here is a couple of the sql statements I have tried, all of which return a blank SELECT box.
Sorry they are all in one row, I wasn't sure how it would space out...
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S WHERE S.Student_ID NOT IN (SELECT CR.Student_ID FROM tblClassRoster as CR WHERE 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')";
and...
$sql = "SELECT DISTINCT S.Student_ID, S.Student_LN, S.Student_FN FROM tblStudent as S LEFT JOIN tblClassRoster as CR ON S.Student_ID = CR.Student_ID WHERE 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' AND CR.Student_ID is null ORDER BY S.Student_LN";
Both of these return a blank SELECT box. I don't believe it is the code to fill the box...the box fills when I do a select * from tblStudent. Also, in the first statement, the subselect works by itself, and the outer select works by itself, but when I add the NOT IN clause, it gives me nothing. I've also tried NOT EXISTS and I've also moved the ANDs in the WHERE statement outside of the subselect. The Student ID is a varchar(6) in both tables. Am I doing something stupid or..... Thanks for any help!!
