OHDSI Home | Forums | Wiki | Github

Outer joins

I have a requirement to associate multiple RXNORM-RXCUI codes for single raw drug exposure when available.
I’m storing the related RXCUI in value_as_string in observation table with concept id 44786713 (Clinical drug component - RxNorm RXCUI of Medication).
To relate them to the related raw drug attributes in the drug_exposure I set the observation obs_event_field_concept_id to 1147707, concept for ‘drug_exposure.drug_exposure_id’,
and store the drug_exposure_id in observadtion_event_id.
My question is for the drugs that I cannot relate to an RXCUI do I need to create a dummy records
in the observation id or does ohdsi allow for outerjoins in implementations.

Can you please explain what are going to achieve doing this excercise so we can better understand your need and help you

We have a lookup table with related scores for each RXCUI that is related to a raw drug. The researchers need to see all these related RXCUIs and scores for the administered drug. We want to make this list available in OMOP rather than keeping it as an external lookup. At the same time we don’t want to store these RXCUIs in the drug exposure as it creates a lot of redundancy.

The outer join behavior is a function of the database and has nothing to do with the OMOP Common Data Model. You do not need to create dummy Observation records.

So, you have a drug administered and some score is related to this admistration, right?
So you create a measurement record with measurement_concept_id that stands for this kind of assessment and connect it to a drug_exposure entry.
You can connect it via MEASUREMENT.modifiers if you work with CDM v5.4 or using fact relationship if you want with 5.3 or earlier version.

Thank you for your suggestion. I’ll work on that option.

What is the best way of connecting my measurement to the drug_exposure. ie. in which field I place the value for drug_exposure_id in the measurement table?

If you’re using v5.4, there are two fields in the measurement table - measurement_event_id and meas_event_field_concept_id for creating such links. In your case, the meas_event_field_concept_id should be populated with 1147094 (a concept for drug_exposure.drug_exposure_id), while the measurement_event_id should be filled with appropriate drug_exposure_id. If your CDM version is 5.3, then use fact_relationship table as @Dymshyts mentioned

Hi Philip,
I am using the CDM v6.0 and these fields are not available. I guess I need to go with cdm v5.4 for now.

Thanks for your suggestions.



For v6.0, yeah, you are limited to the fact_relationship table, or you can extend the measurement table with those two fields. The latter does not seem to be very much a bad idea since similar fields in the observation table (observation_event_id and obs_event_field_concept_id) have been implemented in the v6.0.