java - Creating a SQL query to return values from nominally formed databases -

i attempting return report appointment database. have attached current sql query, difficulty having when "join" customer contacts table ie

 join contacts on (customer.entity_id = contact.contact_id , contacts.arch = 'contact.phonenumber')     join contact_detail on (contact_detail.contact_id = contacts.contact_id , contact_detail.lookup = 'mobile') 

i lose clients didn't have entry on table , detail table, prefer achieve if client has no contact entry returns null fields. ie before added join might have gone

name  time note    mobile david 8pm haircut  0412656865 julie 8pm style daniel 8pm colour  0412533535 

but lines added

david 8pm haircut  0412656865 daniel 8pm colour  0412533535 

it excludes julie because has no contact.phonenumber

thats simple question. prefer julie returned null value

this system uses mysql database java archetype driven front end hence use entity table, act table, participations , detail , lookup tables.

the actual sql query is

select  p.activity_start_time , p.activity_end_time , p.activity_start_time start_time , p.activity_end_time end_time , a.status , a.description appointmentnote , e.name patientname , e.description patientdescription , customer.name customername , customer.description customerdescription , sd.name schedule_name , eat.name appointment_type , mobile.description mobile acts join participations p on   a.act_id=p.act_id , a.arch_short_name='act.customerappointment' ,  p.act_arch_short_name='act.customerappointment' join entities e on p.entity_id=e.entity_id join participations pat on a.act_id=pat.act_id ,  a.arch_short_name='act.customerappointment' ,  pat.arch_short_name='participation.appointmenttype' join entities eat on pat.entity_id=eat.entity_id left join entity_relationships er on (er.target_id = e.entity_id , er.active_start_time <= date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) , ((er.active_end_time >= date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second)) or (er.active_end_time null))) left join entities customer on  (customer.entity_id = er.source_id , customer.arch_short_name = 'party.customerperson')  join contacts mobile on (mobile.party_id = customer.entity_id , mobile.arch_short_name = 'contact.phonenumber') join contact_classifications mb_class on (mb_class.contact_id = mobile.contact_id , mb_class.lookup_id = '120')  join participations schedule on  a.act_id=schedule.act_id , schedule.arch_short_name='participation.schedule' left join entities sd on  (sd.entity_id = schedule.entity_id) left outer join act_details d on  a.act_id=d.act_id (p.activity_start_time<date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) , p.activity_end_time>date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second) or  p.activity_start_time<date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) , p.activity_end_time>date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second) or  p.activity_start_time>=date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) ,  p.activity_end_time<=date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second)) ,  (schedule.activity_start_time<date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) ,  schedule.activity_end_time>date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second) or  schedule.activity_start_time<date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) ,  schedule.activity_end_time>date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second) or  schedule.activity_start_time>=date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "00:00:00" hour_second) ,  schedule.activity_end_time<=date_add((date_format('2013-07-24',"%y-%m-%d")),  interval "23:59:59" hour_second)) ,  e.arch_short_name = 'party.patientpet' ,  customer.arch_short_name = 'party.customerperson' , sd.name concat('groom','%') order p.activity_start_time, sd.name, a.act_id 

try it

join contact_detail on (contact_detail.contact_id = contacts.contact_id , (contact_detail.lookup = 'mobile' or contact_detail.lookup = null)) 

or (it can depend mysql syntax)

join contact_detail on (contact_detail.contact_id = contacts.contact_id , (contact_detail.lookup = 'mobile' or contact_detail.lookup null))