sql server - Looking for similar names and addresses in joined table -

i've been stuck on problem while , i'm hoping can here!

in sql server 2012 have 2 tables, name (containing name , id) , address (containing id , addressid), need find duplicate records based on similar sounding names, , same addressid.

this current code:

select distinct a.id, a.name, c.id, c.name name  join address b      on a.id = b.id join name c      on soundex(a.name) = soundex(c.name)      , a.id < c.id join address d     on c.id = d.id   a.name = c.name  

i returned table contains duplicate information: each pair distinct, don't need know possible pairs, want list of similar names , ids.

i have been trying implement group , having clauses can't seem results want.

any ideas appreciated!

edit:

my current list looks this: id1     name1       id2    name2       addressid 1       smith       10     smythe      8 1       smith       11     smithy      8 1       smith       12     smythe      8 1       smith       13     smithy      8 10      smith       12     smythe      8 10      smith       11     smithy      8 10      smith       13     smythy      8 11      smith       12     smithe      8 11      smith       13     smythy      8 12      smith       13     smythy      8 2       john        14     jon         9 2       john        15     jonn        9 ... 

i want return this:

id1     name1       id2    name2       addressid 1       smith       10     smythe      8 1       smith       11     smithy      8 1       smith       12     smythe      8 1       smith       13     smithy      8 2       john        14     jon         9 2       john        15     jonn        9 ... 

i have managed create rather inelegant solution dumping table in temp table, looping through rows , inserting them in final table if id numbers not yet exist in table. seems me there should better way of doing it, though!

these 2 separate result set, matching address , similar names. need union results

select distinct c.id, c.name, d.id, d.name address inner join address b on a.addressid = b.addressid inner join name c on a.id = c.id inner join name d on b.id = d.id union select a.id, a.name, b.id, b.name name join name b on soundex(a.name) = soundex(b.name)

my comment still valid though. how want output if there more 1 match.

pardon syntax error not try running this.