CRF Table Relationships (Graphical Example from MS Access):
Each row in each CRF table and grouped Item table relates to a particular Study Subject, uniquely identified by Study Subject ID and Study Subject OID. Each CRF table in the Data Mart can be joined to the study_subject_listing table on ssid and ssoid.
Each Item in a group of repeating Items is related to the CRF by the Study Subject ID, Study Subject OID, Study Event OID, Event Ordinal, and CRF Version.
The WHERE clause in the following SELECT statement reflects these joins. When querying data from the database, use WHERE clauses of this form.
SELECT
ssl.ssid,
ae_ungr.event_start_date,
ssl.date_of_birth,
ae_ungr.i_adve_aeyn_label,
ae_grouped.i_adve_aeterm,
ae_grouped.i_adve_aesev
FROM
r01_123456_1_docetaxel_534_items.study_subject_listing ssl,
r01_123456_1_docetaxel_534_items.adverse_events ae_ungr,
r01_123456_1_docetaxel_534_items.adverse_events_ae ae_grouped
WHERE
ssl.ssoid = ae_ungr.ssoid
and
ssl.ssid = ae_ungr.ssid
and
ae_ungr.ssoid = ae_grouped.ssoid
and
ae_ungr.ssid = ae_grouped.ssid
and
ae_ungr.study_event_oid = ae_grouped.study_event_oid
and
ae_ungr.event_ordinal = ae_grouped.event_ordinal
and
ae_ungr.crf_version = ae_grouped.crf_version;