Как выбрать записи, у которых отсутствует запись в присоединенной таблице?

Question

[AlexRas]

Всем привет.

Первая таблица pay:

id
1
2
3

Вторая таблица pay_service:

id | pay_id | service
1 | 1 | cert
2 | 1 | dipl
3 | 2 | cert

Делаю запрос:

SELECT `pay`.*
FROM `pay`
LEFT JOIN `pay_service` ON `pay_service`.`pay_id` = `pay`.`id`
WHERE IFNULL(`pay_service`.`service`, '') NOT IN ('dipl')
GROUP BY `pay`.`id`

Возвращается строки таблицы pay с id:
1, 2, 3

Нужны результаты с id:
2, 3

Нужны записи у которых нет в связанной таблице service = dipl. Можно ли как-то модернизировать подобный запрос? Знаю что есть другие запросы которые вернут правильный результат, но интересует именно возможность это сделать подобным запросом.

Пример рабочих запросов, но они не подходят:

SELECT `pay`.*
FROM `pay`
LEFT JOIN `pay_service` ON `pay_service`.`pay_id` = `pay`.`id` AND `pay_service`.`service` IN ('dipl')
WHERE IFNULL(`pay_service`.`service`, '') NOT IN ('dipl')
GROUP BY `pay`.`id`

SELECT `pay`.*
FROM `pay`
WHERE `id` NOT IN (SELECT `pay_id` FROM `pay_service` WHERE `pay_service`.`service` IN ('dipl'))
GROUP BY `pay`.`id`

SELECT `pay`.*
FROM `pay`
LEFT JOIN `pay_service` ON `pay_service`.`pay_id` = `pay`.`id`
GROUP BY `pay`.`id`
HAVING CONCAT('|', GROUP_CONCAT(IFNULL(`pay_service`.`service`, '') SEPARATOR '|'), '|') NOT LIKE ('%|dipl|%')

Comments

[Дмитрий]

а зачем вам таблица с идами?
Первая таблица pay:
id
1
2
3

[AlexRas]

Дмитрий, а зачем в примере другие поля?

[Abdula Magomedov]

pay_service.pay_id is null

[AlexRas]

Abdula Magomedov, можно подробнее? Если это добавить так

WHERE IFNULL(`pay_service`.`service`, '') NOT IN ('dipl') AND `pay_service`.`pay_id` IS NULL

то вполне ожидаемо возвращается только 3 вариант, но это не то.

[Abdula Magomedov]

AlexRas, а что конкретно не подходит? и почему?

[Abdula Magomedov]

SELECT `pay`.*
FROM `pay`
LEFT JOIN `pay_service` ON `pay_service`.`pay_id` = `pay`.`id` AND `pay_service`.`service` IN ('dipl')
WHERE pay_service.pay_id is null
GROUP BY `pay`.`id`

чем плох этот вариант?

[Abdula Magomedov]

AlexRas,

SELECT `pay`.*
FROM `pay`
LEFT JOIN `pay_service` ON `pay_service`.`pay_id` = `pay`.`id` 
WHERE pay_service.pay_id is null AND `pay_service`.`service` not IN ('dipl')
GROUP BY `pay`.`id`

или этот?

[AlexRas]

Abdula Magomedov, первый вариант мне известен и он работает, но в рамках моей задачи не очень подходит. А второй вообще ничего не возвращает.

[AlexRas]

Abdula Magomedov, задача сделать это, но условия в join не менять

[Abdula Magomedov]

AlexRas, не знаю, только такой вариант наверное тогда

SELECT `pay`.*
FROM `pay`
LEFT JOIN (select * from pay_service where pay_service.service in ('dipl')) as pay_service ON `pay_service`.`pay_id` = `pay`.`id`
WHERE pay_service.pay_id is null
GROUP BY `pay`.`id`

[AlexRas]

Abdula Magomedov, имел ввиду вообще join не трогать

[Abdula Magomedov]

AlexRas, это задача с какого то сайта? Если нет, то откуда такие ограничения?

[AlexRas]

Abdula Magomedov, ищу просто способ, который подойдет лучше всего. С такими ограничениями будет лучший вариант, join может использоваться не только для такой фильтрации, но и других, а там лишние условия не нужны

[Abdula Magomedov]

AlexRas, EXPLAIN