Tutorial :MySQL COUNT problem



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
Previous
Next Post »