Table convention query

[Eileen]

Hi there,

I have been trying to write an SQL query and one field confuses me (well many actually but just one I'm focussed on right now):

`civicrm_contribution`.`payment_instrument_id`

has the description:

FK to Payment Instrument

In other fields with similar descriptions the related field has been the ID field in the table of that name (prepended with civicCRM_) but not in this case so I'm struggling to find the related field.

[Dave Greenberg]

That column is actually keyed to the civicrm_option_value.value - which is where many of the DB stored "enums" are stored.

The option_value rows are grouped by civicrm_option_group.id - each "option group". Payment instruments are have group id of 10. So - you find all of them with:

SELECT *
FROM `civicrm_option_value`
WHERE option_group_id =10

... and add in "value = $yourValue" to get a specific payment instrument for a contribution.