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

Question

I am having a table that maintains the mapping of an EMPLOYEE_ID to the one or more ROLE_IDs 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

I think you meant `ROLE_ID = 2`, not `EMPLOYEE_ID = 2`. – shmosel Jan 29 '16 at 06:06 — shmosel, Jan 29 '16 at 06:06

score 6 · Accepted Answer · edited Jan 25 '16 at 08:22

6

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;

edited Jan 25 '16 at 08:22

deroby

5,902
2
19
33

answered Dec 14 '15 at 10:50

Shadow

33,525
10
51
64

That DISTINCT in the second query may be superfluous assuming that an employee can't be a leader twice.. I'd want a UNIQUE on (employee_id, role_id) just to be sure though. Also the boolean is_team_leader field from the OP fiddle can be simply defined as `ER.ROLE_ID IS NOT NULL` without all the IF stuff. – Arth Jan 28 '16 at 11:48
A single employee may have multiple roles, hence the distinct. Since neither of us know how this company operates or what data is expected in this homework, I leave the distinct there. IF() is used to format the output. – Shadow Jan 28 '16 at 11:56
1

Yeah, multiple rows that are already filtered by your LEFT JOIN, the only problem with leaving the DISTINCT off is if an employee can be listed as a team_leader twice (there is a good probability that this won't be allowed in the spec).. since this is marked as a performance question, I'd make this abundantly clear. Therefore, if the OP follows the advice in Rick James's answer, the DISTINCT can be dropped. – Arth Jan 28 '16 at 12:03
Your `IF()` formatting is just confusing.. a column called `IsISLeader` containing `'NOT IS Leader'` or `'IS Leader'` is horrible. The OP's fiddle has this as a simple 1 or 0 in a column called `is_team_leader` which is much simpler..why complicate this with added less readable syntax? – Arth Jan 28 '16 at 12:06
Well, this is your opinion. Mine is different. – Shadow Jan 28 '16 at 12:14
If there is any interaction from the OP's side indicating things about the data, then I will update my answer. – Shadow Jan 28 '16 at 12:17
I think your answer is otherwise great.. I have upvoted it! I was suggesting adding information so that the OP can choose whether he drops the DISTINCT not removing it entirely from the answer. – Arth Jan 28 '16 at 12:23
1

You are seriously defending a column called `'IsISLeader'`? It almost suggests these are employees working for Daesh! Opinions in query structure and data manipulation aren't all born equal.. Take a look at OP's fiddle, if you deliberately change something like that you should at least have a decent reason. – Arth Jan 28 '16 at 12:28
+1 I was going to write the same answer using `ER.ROLE_ID IS NULL AS IS_TEAM_LEADER` because the output should be the same as what was specified in the question. This modified query, using the left join, is easier to read, but whether or not it is more efficient could be determined using an `EXPLAIN` query – Dave F Jan 29 '16 at 05:37

score 4 · Answer 2 · answered Jan 22 '16 at 17:40

4

"best performing" --

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;

Why.

Beyond that, see @Shadow's answer.

answered Jan 22 '16 at 17:40

Rick James

135,179
13
127
222

score 2 · Answer 3 · answered Jan 27 '16 at 03:41

I include EMPLOYEE_ID in the SQL in case same NAME found for different EMPLOYEE_ID, also use LEFT JOIN in case some employee does not have roles:

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

Asad Ali · Answer 4 · 2016-01-28T18:29:46.930

2

Simply use The FK relationship create a Role Table as

table role{
RoleID
RoleName
Status
...}

and edit the employee table as

table employee{
ID
name
...
}

create table employeerole

table employeerole{
ID
roleID
employeeID

}

now you can manage multiple roles to each employee with normalization rules too

now you can use the query

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

the table role has ID 2 and Name for example is team leader or manager

now this query is fully optimized and will give you best results

edited Jan 28 '16 at 18:29

answered Jan 28 '16 at 18:11

Asad Ali

111
2
7

The employee can have a one or more roles. Hence, including the role in the employee table is not really the ideal design. It violates the normalization principles. – Sarath Chandra Jan 28 '16 at 18:13
Please elaborate how the performance of the given query shall be improved by leveraging the suggestion you propose. – Sarath Chandra Jan 28 '16 at 18:18

score 0 · Answer 5 · answered Jan 29 '16 at 06:04

If I understand you correctly, you're trying to get all employees, with all of their roles, plus an additional column indicating whether they're a Team Leader. If so, you just need to join on EMPLOYEE_ROLE twice, the second being a LEFT JOIN just to check for a specific role:

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

score 0 · Answer 6 · answered Jan 29 '16 at 08:04

0

Here is the query to get all the employee who belongs to a specific role

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

answered Jan 29 '16 at 08:04

Taj Ahmed

895
11
19

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

6 Answers6