CiviCRM Community Forums (archive)

Hi all,
I'm having a brain cramp and can't figure out how to pull some custom data fields into a query I'm using in an extension.

So in broad strokes, I want to return a dataset with civicrm_id,  name, membership type and some other odds and ends. My query is as follows (dumbed down):

SELECT
  c.id AS civicrm_id
, c.last_name AS lastname
, c.first_name AS firstname
, m.id AS membership_id
, m.join_date AS startdate
, mt.name AS member_type

FROM civicrm_contact AS c

INNER JOIN civicrm_membership AS m
ON m.contact_id = c.id

INNER JOIN civicrm_membership_type AS mt
ON mt.id = m.membership_type_id

WHERE m.status_id < 4

Each member also has a couple of custom "Member Fields" that contain info that CiviCRM doesn't account for to cover internal use stuff. I'd like to add to this query to pull that data but I'm not sure if there is a best SQL practice to do that. I can use a blunt force approach to track down the fields by exploring the database but if there is a stored procedure or other semi-automated method I can use, I'd be pretty happy to hear about it.

The particular field I'm looking for is called the "Registration Number" in the "Member Fields" field set. It only applies to a subset of members but it isn't a number, it's an arbitrary series of numbers of letters and it isn't guaranteed to to be unique either. It's a remnant from days gone past but it also isn't going anywhere so I have to work with what I have.

Thanks for any direction.

Andrew   

EDIT: I wanted to add that the custom fields are on the membership records, not the contact records, if that makes any difference.