Query to find which field has improper entry

[jajnsn]

This pertains to MYSQL 5.0.  I have a table that has several columns, including Institution_Name and InstNo.  Each distinct Institution_Name is supposed to have a distinct InstNo.  Therefore, a count of Institution_Name should equal the count of InstNo. However, when I run SELECT COUNT(distinct Institution_Name) the count returned is 221 and when I run SELECT COUNT(distinct InstNo), the value returned is 219.  The 221 count is correct which means, I think, that one or more institutional name entries are erroneously associated with more than one institutional number.  I'm at a loss as to how to write a query that finds this error.  The problem has my head is about to sizzle.  Help please!

[mikosiko]

the simplest way

 

SELECT Institution_name, COUNT(InsNo) 
   FROM <your_table_here> 
   GROUP BY Institution_name
   HAVING COUNT(InsNo) > 1

[jajnsn]

Thanks Mikosito,

 

The command you provided is excellent.  I failed to state that the distinct values in Institution_Name and InstNo repeat hundreds of times in rows distinguished by data in other fields.  This means that the counts returned by the query for InstNo were too high for me to detect where the problem lay.  Fortunately, I had another table that contained all 221 institutional numbers.  So, I solved the problem with a query of which of the institutional numbers in that table were not coming up in my distinct counts on the faulty table, and I need a cold drink of mineral water.

 

Peace!