A
A
AlexRas2019-02-16 15:16:34
MySQL
AlexRas, 2019-02-16 15:16:34

How to select records that don't have an entry in the attached table?

Hello.
First pay table:

id
1
2
3

Second pay_service table:
id | pay_id | service
1 | 1 | cert2
| 1 | dipl3
| 2 | cert

I make a request:
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`

The pay table row with id:
1, 2, 3 is returned.
Results with id:
2, 3 are
needed. Records are needed that do not have service = dipl in the associated table. Is it possible to somehow modernize such a request? I know that there are other queries that will return the correct result, but I'm interested in the possibility of doing this with a similar query.
An example of working queries, but they don't fit:
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

1 answer(s)
V
Vapaamies, 2019-02-17
@vapaamies

select * from pay where not exists (
  select null from pay_service where pay_id = pay.id and service in ('dipl')
)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question