Как выбрать записи из таблицы, одновременно имеющие несколько значений?

Question

[georgich]

Приветствую!
Делаю фильтр для сайта. Приведу пример структуры базы.
1. Таблица employee

2. Таблица services

3. Таблица empl_services

Необходимо сделать запрос на выборку людей, у которых присутствуют выбранные пользователем услуги. Т.е. если на сайте указываем галочками "Крутит гайки" и "Гнёт арматуру", то ни одной записи не будет найдено. А вот если укажем "Крутит гайки" и "Носит ящики", то нашлись бы две записи - Петя и Коля.
Простой запрос, аля

SELECT employee_id FROM empl_services WHERE services_id = 1 AND services_id = 3

по понятным причинам работать не будет. Подскажите, пожалуйста, как решить данную задачу? Спасибо.

Comments

[Антон Р.]

Джоинить.

[Дмитрий]

IN()

[Сергей c0re]

какая версия MySQL ??

[georgich]

Сергей, версия 5.7

[Сергей c0re]

georgich, печаль, на 8ке было бы проще...

[georgich]

Сергей, не вопрос. Можно поднять и до 8-ки. Только подскажите, пожалуйста, с решением.
Спасибо.

[NubasLol]

SELECT employee_id FROM empl_services WHERE services_id = 1 OR services_id = 3

[georgich]

NubasLol, это не то.

[NubasLol]

georgich, почему?

Answer 1

[Сергей c0re]

как-то так:

см. на sqlfiddle

select e.id, e.name
  from employee e
  join (
    select employee_id
      from empl_services es
      join services s on s.id = es.service_id
      where s.title in ('Крутит гайки','Носит ящики')
      group by employee_id
      having count(1) = 2
  ) t on t.employee_id = e.id
;

-- если id услуг известны то так:

select e.id, e.name
  from employee e
  join (
    select employee_id
      from empl_services es
      where service_id in (1,3)
      group by employee_id
      having count(1) = 2
  ) t on t.employee_id = e.id
;

PS: тут обязательно надо в выражении having count(1) = ?? подставлять количество услуг перечисляемых в IN(...)
на 8ке можно по другому через WITH, а в having count(1) подставлять count по подзапросу услуг...

UPD:
на 8ке так:

with 
s as (
  select id from services where title in ('Крутит гайки','Носит ящики')
),
r as (
  select employee_id from empl_services es
    join s on s.id = es.service_id
    group by employee_id
    having count(employee_id) = (select count(1) from s)
)
select *
  from employee e
  join r on r.employee_id = e.id
;

см. на dbfiddle

Comments

[georgich]

По итогу из всех предложенных у вас только рабочее решение.

[Сергей c0re]

georgich, ну так отмечайте его скорее! ))))))

Answer 2

[irishmann]

Создал три таблицы

Кто крутит гайки и носит ящики? (1 и 3 services_id)

Нужны люди и их услуги

Запрос

SELECT
	e.`id` as `employee_id`,
	e.`name` as `employee_name`,
    s.`id` as `service_id`,
    s.`title` as `service_title`
FROM
	`empl_services` es 
    LEFT JOIN `employee` e ON es.`employee_id` = e.`id`
    LEFT JOIN `services` s ON es.`services_id` = s.`id`
WHERE
	s.`id` in(1, 3)

Результат

Нам нужны только люди, убираем лишнее

Запрос

SELECT
	e.`id`,
	e.`name`
FROM
	`empl_services` es 
    LEFT JOIN `employee` e ON es.`employee_id` = e.`id`
WHERE
	es.`services_id` in(1, 3)
GROUP BY e.`id`

Результат

Нужны только идентификаторы, убираем лишнее

Запрос

SELECT
	es.`employee_id`
FROM
	`empl_services` es 
WHERE
	es.`services_id` in(1, 3)
GROUP BY es.`employee_id`

Результат

UPD. Нужны employee у которых есть обe services_id

Запрос

SELECT
	es.`employee_id`,
    e.`name`
FROM
	`empl_services` es
    LEFT JOIN `employee` e ON es.`employee_id` = e.`id`
WHERE
	es.`services_id` in (1, 3)
GROUP BY es.`employee_id`
HAVING (COUNT(*) = 2)

Результат

Comments

[Сергей c0re]

неправильно! надо было и запрос скрином вставить )))))

[irishmann]

Сергей, увы, нельзя))) Запрещено правилами Сайта, пункт 3.8.

[georgich]

В вашем решении лишний Вася. Ибо он Носит ящики, но не крутит гайки.

[irishmann]

georgich, Дополнил

Answer 3

[Иван Шумов]

Select t.*, Count(t.id) z from (select ..... Union select ....) As t Group by t.id having z = 2

Comments

[georgich]

Спасибо за ответ, но не совсем понимаю что в скобках вместо многоточий? Из чего там выборка?

[Иван Шумов]

georgich, там выборка по условию с одним сервисом. Получится в предварительно много одинаковых записей и там уже проверяем сколько дублей. Где дублей столько сколько подзапросов там и правда. Но вообще это может быть болезненно. Это аналитический запрос и если нагрузка большая то проще использовать колоночные базы данных

[georgich]

Иван Шумов, это такое себе решение :)

[Иван Шумов]

georgich, про колоночные базы?)

Answer 4

[Константин Цветков]

Это уже было. В вашем случае нужно соединять псевдонимы по employee_id.

Comments

[Сергей c0re]

имхо, не то ...

[georgich]

Да, как подсказывает Сергей не совсем то.

Answer 5

[Руслан .]

Можно так попробовать:

select en.Name
  from empl_services es
 inner join services ss
         on ss.id     = es.services_id
        and ss.title in ('Крутит гайки', 'Гнёт арматуру')
 inner join employee em
         on ss.id     = es.employee_id
 group by en.Name
having count(1) = 2