
Question:
Is there a way I can count how many ungraded essays there are to be displayed? IF so What else do I have to add or take away from my MySQL code?
Thanks for the help in advance!
Here is my MySQL code.
SELECT students.*, students_essays.* FROM students INNER JOIN students_essays ON students.student_id = students_essays.student_id LEFT JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id WHERE essays_grades.grade_id IS NULL ORDER BY students_essays.id DESC;
Here is my MySQL tables.
CREATE TABLE students_essays ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, student_id INT UNSIGNED NOT NULL, content TEXT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE students ( student_id INT UNSIGNED NOT NULL AUTO_INCREMENT, student_first_name VARCHAR(255) DEFAULT NULL, student_last_name VARCHAR(255) DEFAULT NULL, pass CHAR(40) NOT NULL, PRIMARY KEY (student_id) ); CREATE TABLE essays_grades ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, grade_id INT UNSIGNED NOT NULL, students_essays_id INT UNSIGNED NOT NULL, student_id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ); CREATE TABLE grades ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, letter_grade VARCHAR(2) DEFAULT NULL, grade_points FLOAT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id) );
Solution:1
As far as I can see you only need to look at 2 tables for this.
SELECT COUNT(*) FROM students_essays se WHERE NOT EXISTS(SELECT * FROM essays_grades ge WHERE se.id = eg.students_essays_id)
Solution:2
SELECT count(*) FROM students INNER JOIN students_essays ON students.student_id = students_essays.student_id LEFT JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id WHERE essays_grades.grade_id IS NULL ORDER BY students_essays.id DESC;
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon