Q

Counting multiple intersections

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?

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

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close