mysql join phone calls join with client pissible phones -

i got table named phonecalls call_id (int, aa) , number varchar(50), calldate varchar(50) , 1 named clients id (int, aa), clientname, phone1, phone2, phone3

there might duplicate client names , phones in clients table.

how can display phonecalls records , if number matches part of of 3 phone number fields?

eaxmple data:

phonecalls

1, 12345, 1/1/2016 2, 5555, 2/2/2016 3, 55551. 3/1/2016 4, 888, 8/1/2016  

clients

1, john doe, 0010 123456, 001033333, null 2, joan doe, null, 55557, null  3, sam doe, null, 55558, 55551 4, joan doe, null, 5555, 234234 5. alan doe, 2222, 66666, 8888  

how can results this:

1, 12345, 1/1/2016, 1, john doe, 0010 123456, 001033333, null 2, 5555, 2/2/2016, 2, joan doe, null, 55557, null (only first 1 found matching 1 of phones)  3, 55551. 3/1/2016, null, nulll, null, null  (not found) 4, 888, 8/1/2016, 5. alan doe, 2222, 66666, 8888 

thanks

• left join preserve "not found" cases
• a.number = left(b.phone1,length(a.number)) join on prefix
• or match on phone1,2,3
• group a.call_id keep 1 row per call

query:

select a.*, b.* phonecalls left join clients b on instr(b.phone1, a.number)                      or instr(b.phone2, a.number)                     or instr(b.phone3, a.number)  group a.call_id;