Using IN in mySql

[luc1d1ty]

I can't get the following query to work in mySQL. From what I've read I understood that it was a valid query. It's got nothing to do with the table/column names cause all the parts work fine on their own. If this IN syntax is not supported in mySQL, is there another way to do this?

Thx.

SELECT course.grade, course.subject, quiz.id, quiz.unit, quiz.name
FROM course_student
INNER JOIN course ON (course_student.course=course.id)
INNER JOIN quiz ON (course.id=quiz.course)
WHERE course_student.student='8' AND
WHERE quiz.id NOT IN (
SELECT mark.quiz from mark);

 

[swampBoogie]

The query is valid, it is Mysql that is shortcoming.

SELECT course.grade, course.subject, quiz.id, quiz.unit, quiz.name
FROM course_student
INNER JOIN course ON (course_student.course=course.id)
INNER JOIN quiz ON (course.id=quiz.course )
left outer join mark ON ( quiz.id = mark.quiz )
WHERE course_student.student='8'
AND mark.quiz is null

 

[luc1d1ty]

That looks like it'll work just great but my original query was slightly more complex than I let on. There should also be a WHERE clause in the nested SELECT i.e.

SELECT course.grade, course.subject, quiz.id, quiz.unit, quiz.name
FROM course_student
INNER JOIN course ON (course_student.course=course.id)
INNER JOIN quiz ON (course.id=quiz.course)
WHERE course_student.student='8' AND
WHERE quiz.id NOT IN (
SELECT mark.quiz from mark
WHERE mark.student='8');

Thanks in advance.

 

[swampBoogie]

You have to move that condition to the on clause in the outer join

SELECT course.grade, course.subject, quiz.id, quiz.unit, quiz.name
FROM course_student
INNER JOIN course ON (course_student.course=course.id)
INNER JOIN quiz ON (course.id=quiz.course )
left outer join mark
ON ( quiz.id = mark.quiz and mark.student = '8')
WHERE course_student.student='8'
AND mark.quiz is null

 

[luc1d1ty]

Thanks for your help. I'll give it a go later when I'm at home.