Как проверить, существует ли определенное сопоставление во таблице отображения "один ко многим"

У меня есть таблица, которая поддерживает сопоставление EMPLOYEE_ID с одним или несколькими ROLE_ID, с которыми может быть назначен сотрудник. ROLE_ID является первичным ключом таблицы ROLE.

Теперь я пытаюсь найти, является ли конкретный сотрудник лидером команды (ROLE_ID = 2) или нет. Это, по существу, пытается найти, существует ли конкретная комбинация сопоставления (EMPLOYEE_ID, 2) в таблице сопоставления.

В настоящее время для этого я использую следующий запрос:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
 EXISTS( SELECT 1 FROM `EMPLOYEE_ROLE` WHERE 
   (`EMPLOYEE_ROLE`.`EMPLOYEE_ID` = `E`.`EMPLOYEE_ID`)
   AND (`EMPLOYEE_ROLE`.`ROLE_ID` = 2)) AS `IS_TEAM_LEADER`
 -- Assume some other column shall be selected from ER table, 
 -- hence necessitating the JOIN on ER
FROM EMPLOYEE E
JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
GROUP BY E.EMPLOYEE_ID;

Хотя это, похоже, выполняет эту работу, я ищу более эффективный подход, поскольку подзапрос в его текущей форме кажется излишним. Не уверен, что это актуально, но может FIND_IN_SET или использовать какую-либо такую функцию?

Может ли кто-нибудь предложить решение, поскольку меня интересует наилучший подход?

ИЗМЕНИТЬ 1: Я намеренно использовал JOIN EMPLOYEE_ROLE с намерением, чтобы другой столбец также можно было выбрать из таблицы ER. Итак, я ищу оптимизацию подзапроса, сохраняя при этом целостность. Следовательно, утверждение "текущий подзапрос в его текущей форме кажется излишним".

SQLFiddle: http://sqlfiddle.com/#!9/2aad3/5

Ответ 1

Использовать подзапрос существует или использовать соединение, но вы не должны использовать оба метода в одном запросе.

Я бы использовал подход соединения, поскольку при необходимости можно было получить данные о ролях:

SELECT E.NAME AS `EMPLOYEE_NAME`, 
FROM EMPLOYEE E
INNER JOIN EMPLOYEE_ROLE ER ON (ER.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHERE ER.ROLE_ID=2;

Если вам нужен список всех сотрудников с полем, указывающим, является ли этот сотрудник лидером IS или нет, используйте вместо него левое соединение:

SELECT DISTINCT E.NAME AS `EMPLOYEE_NAME`, IF(ER.ROLE_ID IS NULL, 'NOT IS Leader','IS Leader') AS IsISLeader
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE_ROLE ER ON ER.EMPLOYEE_ID = E.EMPLOYEE_ID AND ER.ROLE_ID=2;

Ответ 2

"лучший результат" -

CREATE TABLE `EMPLOYEE_ROLE` (
  `EMPLOYEE_ID` INT NOT NULL,
  `ROLE_ID` INT NOT NULL,
  PRIMARY KEY(`EMPLOYEE_ID`, ROLE_ID),
  INDEX(`ROLE_ID` EMPLOYEE_ID)
) ENGINE=InnoDB;

Почему.

Помимо этого, см. ответ @Shadow.

Ответ 3

Я включаю EMPLOYEE_ID в SQL, если тот же NAME найден для другого EMPLOYEE_ID, также используйте LEFT JOIN в случае, если у какого-то сотрудника нет ролей:

SELECT E.EMPLOYEE_ID, E.NAME, SUM(IF(R.ROLE_ID=2,1,0)) IS_TEAM_LEADER
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE_ROLE R ON E.EMPLOYEE_ID = R.EMPLOYEE_ID
GROUP BY 1,2

Ответ 4

Просто используйте отношения FK создайте таблицу ролей как

table role{
RoleID
RoleName
Status
...}

и отредактируйте таблицу employee как

table employee{
ID
name
...
}

создать таблицу useeerole

table employeerole{
ID
roleID
employeeID

}

теперь вы можете управлять несколькими ролями для каждого сотрудника с правилами нормализации.

теперь вы можете использовать запрос

SELECT employee.ID, employee.Name, employeerole.roleid FROM role 
INNER   JOIN employeerole ON role.RoleID = employeerole.roleid INNER
JOIN   dbo.employee ON dbo.employeerole.employeeid = dbo.employee.ID 
WHERE   employeerole.roleid = 2

роль таблицы имеет идентификатор 2 и имя, например, руководитель группы или менеджер

теперь этот запрос полностью оптимизирован и даст наилучшие результаты

Ответ 5

Если я правильно вас понимаю, вы пытаетесь привлечь всех сотрудников со всеми их ролями плюс дополнительный столбец, указывающий, являются ли они руководителем группы. Если это так, вам просто нужно присоединиться к EMPLOYEE_ROLE дважды, а второй - LEFT JOIN, чтобы проверить определенную роль:

SELECT E.NAME, ER.ROLE_ID, ER2.EMPLOYEE_ID IS NOT NULL AS `IS_TEAM_LEADER`
  FROM EMPLOYEE E
  JOIN EMPLOYEE_ROLE ER ON ER.EMPLOYEE_ID = E.EMPLOYEE_ID
  LEFT JOIN EMPLOYEE_ROLE ER2 ON ER2.EMPLOYEE_ID = E.EMPLOYEE_ID AND ER2.ROLE_ID = 2;

SQL Fiddle: http://sqlfiddle.com/#!9/2aad3/9/0

Ответ 6

Вот запрос, чтобы получить всех сотрудников, принадлежащих к определенной роли

SELECT e.NAME FROM EMPLOYEE e right join (SELECT EMPLOYEE_ID FROM
EMPLOYEE_ROLE WHERE ROLE_ID=2) er using(EMPLOYEE_ID);