Self Join Comparison

Transact-SQL
1

Hello everyone,

I've been out of the SQL game for about 2 years now but am suddenly being thrust back into the spotlight. If this is a very basic question, my apologies :slight_smile:

I have a table that contains information pertaining to a person. Each person record has the following: Reference Number (unique), First Name, Last Name, Date of Birth, Mothers Maiden Name, Registration Number, Open Case Flag, First Name Soundex, Last Name Soundex, Maiden Name Soundex, Other Identifier 1, Other Identifier 2, Other Identifier 3 and Other Identifier 4.

The end goal of this is to obtain a list of people who are part of an open case and the likelyhood of that person having a duplicate person record in the table based on the various data points. What I would like to do is the following:

  • Get a list of people who have an open case (Open Case Flag is yes)
  • From that list, compare each of the data elements with the full list of people
  • If there are matches it would basically give me the matched results (for example, for each person that is "open" give me the matches based on the date points - so if no matches at all, ignore completely)

Any suggestions on how to do this?

Thanks,

R.

2

welcome back to SQL. Please provide sample data in the form of DDL and DML

create table #muffassa(id int, firstname varchar(50))

insert into #muffassa
select 1, 'Lion' union
select 2, 'Lioness'