Sub Selects or NOT IN...really confused ?!?

jmaccs

Hello all,

I am really confused.... Here is what I have, I hope someone can help!

mysql 5.067

students table
|student_id|first_name|last_name|grade_level

idtrack table
id|timestamp|student_id|event_id

I want to select all the students that ARE NOT in the idtrack table, but in the students table, an exceptions list...essentially this:

...of those students with the defined grade_level and event_id

SELECT students.student_id, students.last_name, students.first_name, students.grade_level, idtrack.event_id
FROM students
LEFT JOIN idtrack ON students.student_id = idtrack.student_id
WHERE (students.grade_level = 11
|| idtrack.student_id IS NULL || idtrack.event_id=6)

every time I try the above query event_id field turns up NULL in the results.
Here is my EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE students ALL NULL NULL NULL NULL 3775
1 SIMPLE idtrack ALL NULL NULL NULL NULL 6 Using where

Any help?

Thanks in advance!
Joe

ixalmida

When you do a JOIN, you are asking for MATCHING records. You cannot find non-matching records with a JOIN.

SELECT student_id, last_name, first_name, grade_level
FROM students
WHERE student_id NOT IN ( SELECT student_id FROM idtrack )

Note that you cannot get "event_id" because you're asking for students that don't exist in the idtrack table.

There may be a more efficient way to query the data, but much depends on your situation.