SarathChandra SarathChandra - 6 months ago 12
SQL Question

How to check if a particular mapping exists in a one-to-many mapping table

I am having a table that maintains the mapping of an

EMPLOYEE_ID
to the one or more
ROLE_ID
s that the employee can be assigned with. The
ROLE_ID
is a primary key of the
ROLE
table.

Now, I am trying to find if a particular employee is a Team Leader (ROLE_ID = 2) or not. That is, in essence, trying to find if the particular mapping combination of (EMPLOYEE_ID, 2) exists in the mapping table.

Currently, I am using the below query to achieve this:

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;


Although this seems to get the job done, I am looking for a more efficient approach, as the subquery in its current form seems redundant. Not sure if it's relevant, but can
FIND_IN_SET
or some such function be used?

Can anyone suggest a solution, as I am interested in the best-performing approach?

EDIT 1: I have intentionally used the
JOIN EMPLOYEE_ROLE
with the intention that some other column also may be picked from the
ER
table. So, I am looking for optimising the subquery, while keeping that join intact. Hence, the statement "current subquery in its current form seems redundant".

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

Answer

Either use the exists subquery or use join, but you should not use both in one query.

I would use the join approach, since it's easy to get role related data if necessary:

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;

If you need a list of all employees with a field indicating if that employee is IS leader or not, then use left join instead of inner:

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;
Comments