T-SQL Query that looks for multiple records.

T-SQL Query that looks for multiple records.

• John Layton

  The general solution is to perform a select that groups by the columns and counts the occurrances. You are interested in the ones with counts greater than 1. That give you the values that are repeated. This result set can be used to find the actual offending records.

  Suppose you suspect that multiple checks have been issued. You have a table that contains check information where the bank account and the check number identify a check.

  Select *
  From paychecks p
  (
  Select account,
  check_no,
  occurs = count(*)
  From paychecks
  Group
  By account,
  check_no
  Having count(*) > 1
  ) dups
  Where p.account = dups.account
  And p.check_no = dups.check_no
  Order
  by p.account,
  p.check_no
  

  
  

• kebo

  select p1.keyfield1 = p1.keyfieldN
  from paycheck p1 left join paycheck p2
  on p1.keyfield1 = p2.keyfield1 and
  p1.keyfield2 = p2.keyfieldN
  where p2.keyfield1 is null
  
  
• T-SQL Query that looks for multiple records.