I am attempting to return a report from an appointment database. I have attached my current sql query, the difficulty I am having is that when I "join" my customer contacts table ie
join contacts on (customer.entity_id = contact.contact_id and contacts.arch = 'contact.phonenumber')
join contact_detail on (contact_detail.contact_id = contacts.contact_id and contact_detail.lookup = 'mobile')
I immediately lose any clients that didn't have an entry on that table and the detail table, what I would prefer to achieve is that the if the client has no contact entry it returns a null for those fields. ie before I added that join it might have gone
Name Time note Mobile
DAVID 8pm haircut 0412656865
Julie 8pm style
Daniel 8pm Colour 0412533535
but with those lines added
DAVID 8pm haircut 0412656865
Daniel 8pm Colour 0412533535
it excludes Julie because she has no contact.phonenumber
Thats the simple question. I would prefer that Julie be returned with a NULL value
This system uses a mysql database with a java archetype driven front end hence we use a entity table, act table, participations and then some detail and lookup tables.
the actual sql query is
select
p.activity_start_time
, p.activity_end_time
, p.activity_start_time AS start_time
, p.activity_end_time as end_time
, a.status
, a.description AS appointmentnote
, e.name as patientname
, e.description AS patientdescription
, customer.name as customername
, customer.description as customerdescription
, sd.name as schedule_name
, eAt.name as appointment_type
, mobile.description as Mobile
from acts a
join participations p on
a.act_id=p.act_id and a.arch_short_name='act.customerAppointment' and
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 and
a.arch_short_name='act.customerAppointment' and
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
AND er.active_start_time <= date_add((date_format('2013-07-24',"%Y-%m-%d")),
INTERVAL "00:00:00" HOUR_SECOND)
AND ((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 IS NULL)))
left join entities customer ON
(customer.entity_id = er.source_id AND customer.arch_short_name = 'party.customerperson')
join contacts mobile on (mobile.party_id = customer.entity_id AND mobile.arch_short_name = 'contact.phoneNumber')
join contact_classifications mb_class on (mb_class.contact_id = mobile.contact_id AND mb_class.lookup_id = '120')
join participations schedule on
a.act_id=schedule.act_id and 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
where (p.activity_start_time<date_add((date_format('2013-07-24',"%Y-%m-%d")),
INTERVAL "00:00:00" HOUR_SECOND)
and 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)
and 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) and
p.activity_end_time<=date_add((date_format('2013-07-24',"%Y-%m-%d")),
INTERVAL "23:59:59" HOUR_SECOND)) and
(schedule.activity_start_time<date_add((date_format('2013-07-24',"%Y-%m-%d")),
INTERVAL "00:00:00" HOUR_SECOND) and
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) and
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) and
schedule.activity_end_time<=date_add((date_format('2013-07-24',"%Y-%m-%d")),
INTERVAL "23:59:59" HOUR_SECOND)) and
e.arch_short_name = 'party.patientpet' and
customer.arch_short_name = 'party.customerperson'
and sd.name like CONCAT('GROOM','%')
order by p.activity_start_time, sd.name, a.act_id
Try it
join contact_detail on (contact_detail.contact_id = contacts.contact_id and (contact_detail.lookup = 'mobile' or contact_detail.lookup = null))
or (it can depend from MySQL syntax)
join contact_detail on (contact_detail.contact_id = contacts.contact_id and (contact_detail.lookup = 'mobile' or contact_detail.lookup is null))