SQL Left Inner Join (mysql)

Question

I have been using the following query:

I am using two tables: (there are some others mentioned but not needed for this question)

assessment_criteria
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| scheme_of_work_id | mediumint(9) | NO   |     | NULL    |                |
| level             | char(255)    | YES  |     | NULL    |                |
| criteria          | char(255)    | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

criteria_completed
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| student_ID             | mediumint(9) | NO   |     | NULL    |                |
| assessment_criteria_id | mediumint(9) | NO   |     | NULL    |                |
| date_marked            | date         | NO   |     | NULL    |                |
| notes                  | varchar(255) | YES  |     | NULL    |                |
| attainment             | varchar(15)  | YES  |     | NULL    |                |
| effort                 | varchar(15)  | YES  |     | NULL    |                |
| marked_by              | varchar(20)  | NO   |     | NULL    |                |
+------------------------+--------------+------+-----+---------+----------------+

I was using a query like this to display a list of assessment criteria that a student HAS NOT completed:

SELECT DISTINCT assessment_criteria.id, assessment_criteria.level, assessment_criteria.criteria FROM assessment_criteria, criteria_completed  
WHERE (assessment_criteria.scheme_of_work_id = '17') 
AND (assessment_criteria.id NOT IN (SELECT criteria_completed.assessment_criteria_id FROM criteria_completed WHERE (student_ID = '403'))) 
ORDER BY level;

This query has become incredibly slow to run, I have been trying to make it faster using LEFT JOIN.

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
ON a.id = b.assessment_criteria_id
WHERE b.assessment_criteria_id IS NULL

But I am having no success when I try to add in clauses for project and student; ie.

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
ON a.id = b.assessment_criteria_id
WHERE b.assessment_criteria_id IS NULL
AND (b.student_ID = '403')
AND (a.scheme_of_work_id = '17');

mysql reports "empty set". I suspect I am referencing these foreign keys incorrectly?

Community · Accepted Answer · 2017-05-23 12:03:46Z

1

(Just to confirm, you are using b.assessment_criteria_id IS NULL to detect failed joins)

Applying the filters on table b to the WHERE clause will filter out any records where the join has failed, which I believe is the cause of the problem.

You can try moving the b filters into the JOIN condition:

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
  ON a.id = b.assessment_criteria_id
  AND (b.student_ID = 403)
WHERE b.assessment_criteria_id IS NULL
  AND (a.scheme_of_work_id = 17);

Although personally, I dislike filtering like this in a JOIN. The alternative would be:

SELECT DISTINCT a.id, a.level, a.criteria 
FROM assessment_criteria a
LEFT JOIN criteria_completed b
  ON a.id = b.assessment_criteria_id
WHERE (a.scheme_of_work_id = 17)
   AND (b.assessment_criteria_id IS NULL OR b.student_ID = 403);

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Jan 22, 2014 at 15:09

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ICT Teacher Over a year ago

Thanks. That seems to be listing all records from assessment_criteria, not just limited to those for scheme_of_work_ID = 17.

ICT Teacher Over a year ago

Really strange though, it works fine as a query in mysql but as soon as I do it from PHP it takes ages to complete the query. :-S

StuartLC Over a year ago

Just a checklist : You'll need indexes on a.scheme_of_work_id, the FK b.assessment_criteria_id and b.student_ID. Also noted that the id columns are all integral - will save a CPU cycle or 2 by removing the quotes :)

ICT Teacher Over a year ago

Yep, I keep on hearing about these index things. I suppose they must be important after all. Will do some more reading. Thanks again.

Collectives™ on Stack Overflow

SQL Left Inner Join (mysql)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related