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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: