CiviCRM Community Forums (archive)

Hello everyone,

I've put together a custom report for a customer (for a mail merge) and everything seems to work mostly well, but I've obviously done something wrong and I need some guidance.

When I run the select, it seems to work well, except that it reports everyone except the people who did the registration. For example, say that Bob registers Carla and David along with himself. Both Carla and David show up but not Bob.

Here's my statement.

SELECT C1.last_name, C1.first_name, C2.display_name AS 'registered_by', E1.email, F1.phone, A1.street_address, A1.city, S2.abbreviation, A1.postal_code, A1.postal_code_suffix, C3.name, P1.register_date, P1.source, P1.fee_level, P1.is_pay_later, S1.name 'status_id', P1.fee_amount FROM civicrm_participant AS P1 INNER JOIN civicrm_contact AS C1 ON C1.id = P1.contact_id INNER JOIN civicrm_email AS E1 ON C1.id = E1.contact_id INNER JOIN civicrm_participant_status_type AS S1 ON P1.status_id = S1.id INNER JOIN civicrm_participant AS P2 ON P1.registered_by_id = P2.id INNER JOIN civicrm_contact AS C2 ON P2.contact_id = C2.id INNER JOIN civicrm_phone AS F1 ON C1.id = F1.contact_id INNER JOIN civicrm_address AS A1 ON C1.id = A1.contact_id INNER JOIN civicrm_state_province AS S2 ON A1.state_province_id = S2.id INNER JOIN civicrm_country AS C3 on A1.country_id = C3.id WHERE P1.status_id IN (1,5) GROUP BY P1.id ORDER BY P1.status_id, P1.register_date;

Can anyone tell me where I've gone wrong?

-- Marcel

Hey dlobo,

Not trying to spam the forums. I noticed, after the fact, that there was a CiviEvent forum and so I thought I should post the question here.

-- Marcel