MySQL query problem.

Question

So far I can list all the students essays and graded student essays and was wondering how would I be able to display all ungraded essays? What else do I have to add to my MySQL code?

Thanks for the help in advance!

Here is the code I have so far.

SELECT students.*, students_essays.*  
FROM students  
INNER JOIN students_essays ON students.student_id = students_essays.student_id 
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
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)
);

Answer 1

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.students_essays_id is null
ORDER BY students_essays.id DESC  

Answer 2

Is ungraded a letter_grade, e. g. 'UG'?

In this case, add

WHERE letter_grade = 'UG'

before the ORDER BY.

Answer 3

SELECT students.*, students_essays.*  
FROM students  
INNER JOIN students_essays ON students.student_id = students_essays.student_id 
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
INNER JOIN grades ON essays_grades.grade_id = grades.id
WHERE LETTER_GRADE IS NULL
ORDER BY students_essays.id DESC;

I used INNER JOIN, just like you did. Note that an inner join will hide rows in the left table, if no matching entry in the right table is found. Use left join if you want to include those as well.

I assumed that letter_grade is null if the essay is ungraded. If your database will not have a essays_grades row, if an essay is not graded, the query will be simpler:

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;