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

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 ou开发者_C百科t with this?

Thanks!

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

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

Just join the table back on itself and use your criteria as the join criteria:

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