MySQL COUNT problem.

Question

I'm trying to count all the graded essays but if an essay has been graded twice or more for some reason the code will count the same essay as many times it has been graded is there a way I can make sure that the essay is counted only once?

Thanks for the help in advance!

Here is the code.

SELECT COUNT(students_essays.id) 
FROM students_essays 
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id 

Here is my MySQL code.

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)
);

THANKS everyone for the help I figured out the problem It wasn't even this query doh!

Answer 1

SELECT COUNT(DISTINCT students_essays.id)

Answer 2

Use a LEFT JOIN insted of INNER JOIN

EDIT: Meaning, SELECT essays, so you only have 1, and LEFT JOIN that to marks

Answer 3

Add DISTINCT to your query. Or, if you only need the id, don't bother with the students_essays table at all since the id is in essays_grades:

select count(distinct students_essays_id) from essays_grades