I have a situation where I have resolved a many-to-many relationship (employees and roles) with an intersection table. This table contains two foreign keys. How would I write the query to return the number of employees with multiple roles or the number of roles associated with multiple employees?

    Requires Free Membership to View

    Login

Like this:

select count(*) as 
           number_of_empls_with_multiple_roles
  from (
       select e.id
            , e.name
         from employees as e
       inner 
         join employee_roles as er
           on e.id = er.empl_id
       group
           by e.id
            , e.name
       having count(*) > 1
       ) as empls_with_multiple_roles

And this:

select count(*) as 
           number_of_roles_with_multiple_empls
  from (
       select r.id
            , r.name
         from roles as r
       inner 
         join employee_roles as er
           on r.id = er.role_id
       group
           by r.id
            , r.name
       having count(*) > 1
       ) as roles_with_multiple_empls

Run the subqueries by themselves to assure yourself that they return the correct data to be counted.

This was first published in June 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top