T-SQL Check for duplicate field1 values but different field2 values

Question

My scenario is this:

I have a table with a structure like this (simplified) -

CREATE TABLE [dbo].[pe_mem](
    [pm_member] [int] NULL,
    [pm_surname] [char](50) NULL,
    [pm_forename] [char](50) NULL,
    [pm_rsi_num] [char](11) NULL

) ON [PRIMARY]

I need to run a query to find all rows that have an identical pm_rsi_num but a different pm_surname.

Can anyone help me out with this?

Thanks!

Answer 1

You can use a self join for that:

select  *
from    pe_mem t1
join    pe_mem t2
on      t1.pm_rsi_num = t2.pm_rsi_num
        and t1.pm_surname <> t2.pm_surname

Answer 2

Exists variant:

select  *
from    pe_mem t1
where exists
(select null
 from   pe_mem t2
 where  t1.pm_rsi_num = t2.pm_rsi_num
        and t1.pm_surname <> t2.pm_surname)

Single table scan version:

select pm_rsi_num
from pe_mem
group by pm_rsi_num
having count(distinct pm_surname) > 1