mysql - How can I link account in a parent/child relation using address or phone number? -

i trying link accounts in parent child relationship

i have accounts in accounts table each account has own id "primary number", "group_id", phone, contact, email, fax.

i need select accounts has parents.

here how define parent: first group_id must same , meets @ least 1 of following criteria

1. if address same
2. if phone number same
3. if fax same
4. if email same
5. if first 6 chars of address same

i have created fiddle explain trying http://sqlfiddle.com/#!2/1974f/5

here code

create table accounts(   id int(11),   name varchar(255),   address1 varchar(255),   phone varchar(10),   fax varchar(10),    email varchar(255),   contact varchar(255),   group_id int(11)   ); insert accounts(id, name, address1, phone, fax, email, contact, group_id) values (1, 'program 1', '123 main s','7542116654','7456113354','test@test.com','blah blah',10), (2, 'program 2', '123 main south','745123456','7024568984','none@test.com','james',10), (3, 'program 100', '4512 1st ave','754219648','7452155546','jim@test.com','jim',13), (4, 'program 55', '12645 west industry ave','7023665520','7981234568','mike@test.com','mike',15), (4, 'program trop', '465431 e tropicana ave','7023665520','7024564546','jay@test.com','jay',10); 

this attempt pull data

select b.id parent, a.* accounts inner join(   select * accounts ) b on a.id= b.id a.address1 = b.address1 or left(a.address1 , 6) = left(b.address1, 6) or (a.phone = b.phone , length(b.phone) = 10) or (a.fax = b.phone , length(b.fax) = 10) or (a.contact = b.contact , length(b.contact) > 5 ) or (a.email = b.email , length(b.email) > 5 ) 

the final output should give me same data in account table column parent id if any.

how can this?

a query ask is:

select   a1.*, a2.id parent   accounts a1   left join accounts a2 on a1.group_id = a2.group_id     , (       a1.address1 = a2.address1       or a1.phone = a2.phone       or a1.fax = a2.fax       or a1.email = a2.email       or (         char_length(a1.address1) >= 6         , char_length(a2.address1) >= 6         , left(a1.address1, 6) = left(a2.address1, 6)       )     ) 

however, note conditions mean account parent of , if account parent of account b b parent of account a.

you can prevent adding and a1.id <> a2.id (if want prevent accounts being own parent) or and a1.id > a2.id (if want prevent accounts being own parent , prevent relation going both ways).