I am trying to edit a report which using the following query.

I want it to pick up all contacts such as mobiles, home phones, etc but it just seems to get the first listed contact number for each customer. What do I need to do to get it to join all contact details?

Thanks in advance,

Matt.

select
  c.entity_id as id,
  c1.value as lastname,
  c2.value as firstname,
  l3.name as title,
  cd1.value as address,
  l2.name as suburb,
  cd3.value as postcode,
  cd4.value as state,
  ced1.value as email,
  cfd1.value as phoneNumber,
  cfd2.value as sms,
  p.name as patient,
  l4.name as species,
  l5.name as breed,
  date_format(replace(d1.value," EST",""),"%d/%m/%Y") as DOB,
  d6.value as sex,
  d2.value as desexed,
  max(pa.activity_start_time) as lastsale
from
  acts pa
  join act_relationships ar on pa.act_id = ar.source_id
  join acts ca on ar.target_id = ca.act_id and ca.arch_short_name like "act.customerAccount%Item"
  join participations pp on ca.act_id = pp.act_id
  join entities pr on pr.entity_id = pp.entity_id and pr.arch_short_name like "product.%"
  left join entity_relationships rp on rp.target_id = pr.entity_id and rp.arch_short_name = "entityRelationship.productTypeProduct"
  left join entities pt on pt.entity_id = rp.source_id
  join participations pap on ca.act_id = pap.act_id and pap.arch_short_name = "participation.patient"
  join entities p on p.entity_id = pap.entity_id
  join participations cp on pa.act_id = cp.act_id and cp.arch_short_name = "participation.customer"
  join entities c on c.entity_id = cp.entity_id
  left outer join entity_details c1 on c1.entity_id = c.entity_id and c1.name = "lastName"
  left outer join entity_details c2 on c2.entity_id = c.entity_id and c2.name = "firstName"
  left outer join entity_details c3 on c3.entity_id = c.entity_id and c3.name = "title"
  left outer join lookups l3 on l3.code = c3.value and l3.arch_short_name = "lookup.personTitle"
  left outer join contacts cc on cc.party_id = c.entity_id and cc.arch_short_name = "contact.location"
  left outer join contact_details cd1 on cd1.contact_id = cc.contact_id and cd1.name = "address"
  left outer join contact_details cd2 on cd2.contact_id = cc.contact_id and cd2.name = "suburb"
  left outer join lookups l2 on cd2.value = l2.code and l2.arch_short_name = "lookup.suburb"
  left outer join contact_details cd3 on cd3.contact_id = cc.contact_id and cd3.name = "postcode"
  left outer join contact_details cd4 on cd4.contact_id = cc.contact_id and cd4.name = "state"
  left outer join contacts ce on ce.party_id = c.entity_id and ce.arch_short_name = "contact.email"
  left outer join contact_details ced1 on ced1.contact_id = ce.contact_id and ced1.name = "emailAddress"
  left outer join contacts cf on cf.party_id = c.entity_id and cf.arch_short_name = "contact.phoneNumber"
  left outer join contact_details cfd1 on cfd1.contact_id = cf.contact_id and cfd1.name = "telephoneNumber"
  left outer join contact_details cfd2 on cfd2.contact_id = cf.contact_id and cfd2.name = "sms"
  left outer join entity_classifications ec on ec.entity_id = c.entity_id
  left outer join lookups l on l.lookup_id = ec.lookup_id and (l.arch_short_name = "lookup.customerAccountType" || l.arch_short_name = "lookup.customerType")
  left outer join entity_details d1 on d1.entity_id = p.entity_id and d1.name = "dateOfBirth"
  left outer join entity_details d2 on d2.entity_id = p.entity_id and d2.name = "desexed"
  left outer join entity_details d3 on d3.entity_id = p.entity_id and d3.name = "deceased"
  left outer join entity_details d4 on d4.entity_id = p.entity_id and d4.name = "species"
  left outer join lookups l4 on l4.code = d4.value and l4.arch_short_name = "lookup.species"
  left outer join entity_details d5 on d5.entity_id = p.entity_id and d5.name = "breed"
  left outer join lookups l5 on l5.code = d5.value and l5.arch_short_name = "lookup.breed"
  left outer join entity_details d6 on d6.entity_id = p.entity_id and d6.name = "sex"
where
  pa.arch_short_name like "act.customerAccountCharges%" and
  pa.activity_start_time between $P{Sale Start} and date_add($P{Sale End}, INTERVAL "23:59:59" HOUR_SECOND)and
  pr.name like $P{Product} and
  pt.name like $P{Product Type} and
  p.active = 1 and
  c.active = 1 and
  l4.name like $P{Species} and
  d5.value like $P{Breed} and
  d1.value between $P{DOB Start} and $P{DOB End}
group by c.entity_id
order by c.name