Sub Select 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:

 

|student_id|first_name|last_name|grade_level|event_id

 

...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

 

 

[mikosiko]

try this

 

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
      AND ( idtrack.student_id IS NULL  OR idtrack.event_id=6)

 

this is getting all the student with grade_level 11 who, are not present in the idtrack table or those with event_id = 6