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 having identical pm_rsi_num, but another pm_surname.

Can anyone help me with this?

Thank!

Answer 1

There is an option:

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

Answer 2

For this you can use self-connect:

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 3

:

select * from pe_mem as p1
inner join pe_mem as p2
on p1.pm_rsi_num = p2.pm_rsi_num
    and p1.pm_surname <> p2.pm_surname