CiviCRM Community Forums (archive)

I have come up with the following SQL for seeing what was sold via price sets. Can anyone verify that I am on the right track?
Particularly I am wondering if I am right in using the civicrm_participant_payment table.
I could of course select any fields and it is the joins I am more concerned with.

-- Contacts and what they bought from a price set used for general contributions.
-- Might want to bring in contribution type and bookkeeping code

SELECT
CC.`contact_type`, CC.`display_name`, CC.`first_name`,CC.`last_name`,
CCB.receive_date AS TRANSACTON_DATE, CCB.receipt_date, CCB.trxn_id,
CCB.invoice_id, CCB.currency, CCB.cancel_date,
CPS.title, CPF.LABEL,
CPFV.label AS CURRENT_OPTION_LABEL,
CPFV.amount AS CURRENT_OPTION_PRICE,
CLI.label AS SOLD_OPTION_LABEL,
CLI.qty, CLI.unit_price AS SOLD_OPTION_PRICE, CLI.line_total
FROM civicrm_line_item CLI
INNER JOIN civicrm_price_field_value CPFV
ON price_field_value_id = CPFV.id
INNER JOIN civicrm_price_field CPF
ON CPFV.`price_field_id` = CPF.id
INNER JOIN civicrm_price_set CPS
ON CPF.price_set_id = CPS.id
INNER JOIN civicrm_contribution CCB
ON CLI.entity_id = CCB.id
INNER JOIN civicrm_contact CC
ON CCB.contact_id = CC.id
WHERE entity_table = 'civicrm_contribution'
ORDER BY receive_date;

-- Contacts and what they paid for events using prices sets.
-- Probably want to bring in Event Name, Role, and statusid
-- If there are events that do not use price sets they will not YET show up in this report
-- A modified version could be made to show them
SELECT
CC.`contact_type`, CC.`display_name`, CC.`first_name`,CC.`last_name`,
CCB.receive_date AS TRANSACTON_DATE, CCB.receipt_date, CCB.trxn_id,
CCB.invoice_id, CCB.currency, CCB.cancel_date,
CCP.register_date,  CCP.fee_currency,
CPS.title, CPF.LABEL,
CPFV.label AS CURRENT_OPTION_LABEL,
CPFV.amount AS CURRENT_OPTION_PRICE,
CLI.label AS SOLD_OPTION_LABEL,
CLI.qty, CLI.unit_price AS SOLD_OPTION_PRICE, CLI.line_total
FROM civicrm_line_item CLI
INNER JOIN civicrm_price_field_value CPFV
ON price_field_value_id = CPFV.id
INNER JOIN civicrm_price_field CPF
ON CPFV.`price_field_id` = CPF.id
INNER JOIN civicrm_price_set CPS
ON CPF.price_set_id = CPS.id
INNER JOIN civicrm_participant CCP
ON CLI.entity_id = CCP.id
INNER JOIN civicrm_participant_payment CCPP
ON CCP.id = CCPP.participant_id
INNER JOIN civicrm_contribution CCB
ON CCPP.contribution_id = CCB.id
INNER JOIN civicrm_contact CC
ON CCB.contact_id = CC.id
WHERE entity_table = 'civicrm_participant'
ORDER BY register_date
Thanks for taking a look.