MySQL query problem.

Question

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

Thanks for the help in advance!

Here is what I got so far with my MySQL code.

SELECT students.*, students_essays.* 
FROM students 
INNER JOIN students_essays ON students.student_id = students_essays.student_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

Just join the grades table on the essays table and check for an existing record.

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

Answer 2

Just inner join to the essays_grades table (and the grades table if you want the grade).

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's a few tips... You can alias table names (by providing the alias after the table name in the FROM clause) and stop referring to them by their full names, and you can just say JOIN and it is implied to be an inner join.

SELECT st.*, es.*  
FROM students st  
JOIN students_essays es ON st.student_id = es.student_id 
JOIN essays_grades gr ON es.id = gr.students_essays_id
ORDER BY es.id DESC