SQL: tell whether columns are unique with respect to each other

Question

Suppose I've got a table:

Role
----
person_id company_id financial_year

How can I tell the following:

1. Whether each person_id occurs at most once per company_id per financial_year in this table
2. If 1. is false, which person_id's and company_id's and financial_year's co-occur more than once

Edit 1: Edited to add financial_year col

Edit 2: the RDBMS platform here happens to be MySQL, though I don't expect that this would require much vendor-specific SQL

Answer 1

This should do what you need:

select left.person_id, left.company_id, left.financial_year, count(*)
from role left
inner join role right
    on left.person_id = right.person_id 
        and left.company_id = right.company_id
        and left.financial_year = right.financial_year
group by left.person_id, left.company_id, left.financial_year

Note that this is T-SQL (MS), but the only thing I know of that might change is the table alias syntax, as the rest is ANSI SQL. This will return one row per repeated person/company/year combination, with a count of the number of times that combination is repeated (though the count wasn't mentioned in the question, I know it can sometimes be useful).

Answer 2

For the first, it's generally a good idea to just have a grouping which you can then filter on if you want:

select
  r.company_id, r.person_id, r.financial_year, count(r.person_id) 
from
  Role as r 
group by
  r.company_id, r.person_id, r.financial_year

For the second, you can just modify the above like so:

select
  r.company_id, r.person_id, r.financial_year, count(r.person_id) 
from
  Role as r 
group by
  r.company_id, r.person_id, r.financial_year
having
  count(r.person_id) > 1

Answer 3

I think this will do it for #1:

select count(*), count(distinct person_id, company_id, financial_year)
    from role

(Edit: If the two count()'s are different, then the table contains multiple rows per unique combination of the three columns, what I was asking in question #1. Take the difference of them to get the number of such rows.)

and casperOne's answer will do it for #2

Answer 4

Yes, in general, to detect duplicates,

Select [ColumnList you want to be unique]
From Table
Group By [SameColumn List]
Having Count(*) > 1

In your specific case

Select person_id, company_id, financial_year
From Table
Group By person_id, company_id, financial_year
Having Count(*) > 1

or, for your sub-question (1) about Whether each person_id occurs at most once per company_id per financial_year in this table

Select company_id, financial_year
From Table
Group By company_id, financial_year
Having Count(Person_Id) > 1

and for (2): (when (1) is false, which person_id's and company_id's and financial_year's co-occur more than once

Select person_id, company_id, financial_year
From Table T
Where Not Exists 
     (Select * From Table
      Where company_id = T.company_id 
         And financial_year = T.financial_year          
      Having Count(Person_Id) > 1)
Group By person_id, company_id, financial_year
Having Count(*) > 1