Answer the question
In order to leave comments, you need to log in
How to select records that don't have an entry in the attached table?
Hello.
First pay table:
id
1
2
3
id | pay_id | service
1 | 1 | cert2
| 1 | dipl3
| 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`
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|%')
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question