MySQL查询 - 显示不在另一个表中的结果

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?

但是,我想修改它,以便它只显示其中lesson.id不在表invoice_lesson.lesson_id中的结果。这是一个相关的查询吗?我该怎么做呢?

3 个解决方案

#1

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.

这种方法比相关子查询方法快得多。

#2

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

#3

Try using a JOIN:

尝试使用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

#1