General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I am writing an app for a school. I want to point to an entry in the Schedule table and see all of the students enrolled in the target class. Not unreasonable, right? The relationship is many-to-many between the Student and Schedule tables, with the Enroll table bridging them. Here's the SQL from the p-view:
SELECT Schedule.term, Schedule.discipline, Schedule.level,;
Schedule.section, Student.name, IIF(Enroll.drop,"Dropped",""),;
Schedule.pkey, Enroll.pkey, Student.pkey;
FROM ieli!schedule INNER JOIN ieli!enroll;
INNER JOIN ieli!student ;
ON Enroll.stufkey = Student.pkey ;
ON Schedule.pkey = Enroll.schedfkey;
WHERE Schedule.term = ?vTerm;
AND Schedule.level = ?vLevel;
AND Schedule.section = ?vSection;
AND Schedule.discipline = ?vDiscipline;
ORDER BY Student.name
Most of the classes in this school are paired by discipline: for every Term + Level + Section there is a COMM discipline and a WRIT discipline class.
Here's the crazy part: if the discipline is WRIT, I get nobody. If it is COMM, I get all of the students for both the COMM and the WRIT classes. SET FILTER produces the correct results in the Enroll table.
I have tried this without the two keywords (Level & Name)--same results. Any ideas out there?? ARGH!!!
TIA,
John
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only