Working with Enumerations

The attached excel spreadsheet file lists enumerations in the ZEPRS application. It includes the field_id for each enumeration. The field_id can be used to join to the corresponding form_field.

SQL to generate the excel spreadsheet:

select id, field_id, enumeration from admin.field_enumeration;

Example:

The following SQL demonstrates a join between the patientregistration table and the enumeration table to present the string values for the enumerations (dropdowns).

select pr.id, surname_6, forenames_7, fe1.enumeration AS marital_status 
from patientregistration pr, admin.field_enumeration fe1 
where fe1.id = pr.marital_stat_10
and (pr.marital_stat_10 = 8 or pr.marital_stat_10 = 8)
limit 10;

+------+-------+-------------+----------------+
| id   | Jones | forenames_7 | marital_status |
+------+-------+-------------+----------------+
| 6138 | Jones | Atiness     | Married        |
| 6502 | Jones | Primrose    | Married        |
| 8845 | Jones | Jenipher    | Married        |
| 8846 | Jones | Linda       | Married        |
| 8848 | Jones | Grace       | Married        |
| 8859 | Jones | Vinus       | Married        |
| 8860 | Jones | Dorothy     | Married        |
| 8868 | Jones | Margret     | Married        |
| 8872 | Jones | Harriet     | Married        |
| 8880 | Jones | Queen       | Married        |
+------+-------+-------------+----------------+