How to limit in this SQL Query
-
someone wrote this query for me and we need to filter on the “Group_concat(mem_type. name)AS Member_Type” field so we only get data where this field contains (New Jersey). There are other values in this field so I have to keep the CONCAT but then I want to only show records that have “New Jersey”.
________________
SELECT DISTINCT Group_concat(membership.id),contact.first_name,
contact.middle_name,
contact.last_name,
contact.job_title,
contact.organization_name,
address.street_address,
address.supplemental_address_1,
(SELECT name
FROM civicrm_state_province
WHERE id = address.state_province_id) AS state,
address.city,
address.postal_code,
phone.phone,
Group_concat(mem_type. name) AS Member_Type,
Group_concat(membership.join_date) AS since_date,
Group_concat(membership.start_date),
Group_concat(membership.end_date),
(SELECT name
FROM civicrm_membership_status
WHERE id = Group_concat(membership.status_id)) AS
membership_status,
contribution.total_amount,
email.email
FROM civicrm_membership membership
LEFT JOIN civicrm_membership_type mem_type
ON mem_type.id = membership.membership_type_id
LEFT JOIN civicrm_contact contact
ON contact.id = membership.contact_id
LEFT JOIN civicrm_address address
ON address.contact_id = contact.id
AND address.is_primary = 1
LEFT JOIN civicrm_phone phone
ON phone.contact_id = contact.id
AND phone.is_primary = 1
LEFT JOIN civicrm_membership_payment payment
ON payment.membership_id = membership.id
LEFT JOIN civicrm_contribution contribution
ON contribution.id = payment.contribution_id
LEFT JOIN civicrm_email email
ON email.contact_id = contact.id
AND email.is_primary = 1
WHERE membership.status_id IN ( 1, 2, 3 )
AND membership.contact_id IN (SELECT contact_id
FROM civicrm_membership
WHERE membership.status_id IN ( 1, 2, 3 )
GROUP BY contact_id)
GROUP BY membership.contact_id
________________
- The topic ‘How to limit in this SQL Query’ is closed to new replies.
