How to get any one record from a table, with the condition that there will be no record with a certain value in the associated table?

Question

MySQL

There is an `order` table and a `task` associated with it.
I need to get any one order that is not fulfilled by a certain executor (let's say with ID: 5)

Structure and relationships of tables

I wrote the following query:

SELECT `order`.* FROM `order` 
LEFT JOIN `task` ON `order`.`id` = `task`.`order_id` 
WHERE (`order`.`status`=10) AND
(`order`.`type`='10') AND
(`task`.`executor_id` != 5)
LIMIT 1

It didn't work, it looks like we can make an error:
a) If there are no entries in "task" at all: no entry is found.
b) If there is an entry in "task": there is an entry that has already been given to the current executor (executor_id).

Question : How to get any one record from a table, provided that there is no record with a certain value in the associated table? Can a subquery be used?

Question within this task: (for ease of understanding) How to get an entry from the `order` table that does not have an associated entry in the `task` table with a specific `task`.`executor_id`?

An example if confused in terms

У нас есть 2 заказа: "Собрать яблока", "Собрать груши" и исполнитель Петя.
Петя уже взял заказ "Собрать яблока", и просит ещё один. Давать Пете повторно заказ "Собрать яблоки" не нужно - он ведь уже его взял, значит нужно дать заказ "Собрать груши".

Answer 1

Instead
of (`task`.`executor_id` != 5)
you need to write
(`task`.`executor_id` is null)
where 5 came from is completely incomprehensible.

Answer 2

I can’t speak for mysql, but in general, modern DBMSs have enough intelligence to build an optimal execution plan even from a literal translation of the conditions description

select * from tbl1 where tbl1.x not in (select  x from tabl2 where ...)