MySQL query - show results that are not in another table

Question

I have a query like this:

SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
WHERE lesson.student = p1.id
AND   lesson.teacher = p2.id
AND   instrument.id  = lesson.instrument_id
ORDER BY surname

However, I would like to modify it so that it only shows results where lesson.id is not in the table invoice_lesson.lesson_id. Is this a correlated query? How do I do this?

Answer 1

The easiest way:

SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
WHERE lesson.student = p1.id
AND   lesson.teacher = p2.id
AND   instrument.id  = lesson.instrument_id
AND   lesson.id NOT IN (SELECT lesson_id FROM invoice_lesson)
ORDER BY surname

Might not exactly be the quickest one :)

Tomasz Kopczuk

Answer 2

You can do this with an outer join:

SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
FROM  lesson l JOIN person p1 ON l.student = p1.id
JOIN person p2 ON l.teacher = p2.id
JOIN instrument i ON i.id = l.instrument_id
LEFT JOIN invoice_lesson il ON l.id = il.lesson_id
WHERE il.lesson_id IS NULL
ORDER BY surname

This approach will be much faster than the correlated subquery approach.

Answer 3

Try using a JOIN:

 SELECT lesson.id, p1.first_name, p1.surname, start_time, instrument.name 
 FROM  lesson, person AS p1, person AS p2, instrument, invoice_lesson
 JOIN  invoice_lesson
 ON    lession.id = invoice_lession.lesson_id
 WHERE lesson.student = p1.id
 AND   lesson.teacher = p2.id
 AND   instrument.id  = lesson.instrument_id
 ORDER BY surname