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 on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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