1

I have what seems like a basic sql question. I have a many to many relationship through a junction table. Say Employees to Roles through EmployeeRoles

Table: EMPLOYEES
Columns: id, name, etc.

Table: ROLES
Columns: id, name

Table: EMPLOYEES_ROLES
Columns: employee_id, role_id

How do I query for an employee who has two roles, say Admin and Supervisor? Bonus points in Hibernate.

David T
  • 765
  • 6
  • 18

2 Answers2

2

You can try below -

select a.emp_id,b.name from employee_roles a
inner join EMPLOYEES b on a.emp_id=b.id
inner join roles c on a.role_id=c.id
where c.name in ('Admin' ,'Supervisor') and not exists 
     (select 1 from employee_roles a1
             inner join EMPLOYEES b1 on a1.emp_id=b1.id
             inner join roles c1 on a1.role_id=c1.id
             where a.emp_id=a1.emp_id and c1.name='Trainee')
group by a.emp_id,b.name
having count(distinct c.name)=2
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • ok, bonus points, how would I query for those with roles of 'Admin' and 'Supervisor' but did not also have a role of 'Trainee', for example. – David T Mar 07 '19 at 21:43
1

If you don't need to know which specific roles the employees have, you don't even need to access the roles table; just get a list of which employee_id numbers have multiple records in employee_roles, and join those employee_id values to employees:

select e.name, count(x.role_id) as num_roles
from employee_roles x
join employees e
on (e.id = x.employee_id)
having count(x.role_id) >= 2;
Don Del Grande
  • 140
  • 10