C04: Translate a MedDRA concept into a SNOMED-CT concept

This query accepts a MedDRA concept ID as input and returns details of the equivalent SNOMED-CT concepts.
The existing relationships in the vocabulary associate MedDRA ‘Preferred Term’ to SNOMED-CT ‘clinical findings’. The respective hierarchy for MedDRA and SNOMED-CT can be used to traverse up and down the hierarchy of each of these individual vocabularies.

 Parameter  Example  Mandatory  Notes
 MedDRA Concept ID  35205180  Yes Concept Identifier for ‘Acute myocardial infarction’
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following is a sample run of the query to list all MedDRA concepts that have SNOMED-CT equivalents. Sample parameter substitution is highlighted in blue.
SELECT  D.concept_id              MedDRA_concept_id,
        D.concept_name            MedDRA_concept_name,
        D.concept_code            MedDRA_concept_code,
        D.concept_class           MedDRA_concept_class,
        A.concept_id              Snomed_concept_id,
        A.concept_name            Snomed_concept_name,
        A.concept_code            Snomed_concept_code,
        A.concept_class           Snomed_concept_class
FROM   vocabulary.concept_relationship  CR,
       vocabulary.concept               A,
       vocabulary.concept               D,
       vocabulary.relationship          RT
WHERE  CR.relationship_id = 125
AND    CR.concept_id_2    = A.concept_id
AND    CR.concept_id_1    = 35205180
AND    CR.concept_id_1    = D.concept_id
AND    CR.relationship_id = RT.relationship_id
AND    sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date
Output field list:
 Field  Description
 MedDRA_Concept_ID  Concept ID of MedDRA concept entered as input
 MedDRA_Concept_Name  Concept name of MedDRA concept
 MedDRA_Concept_Code  Concept code of MedDRA concept
 MedDRA_Concept_Class  Concept class of MedDRA concept
 Relationship_ID  Identifier for the type of relationship
 Relationship_Name  Description of the type of relationship
 SNOMED-CT_Concept_ID  Concept ID of matching SNOMED-CT concept
 SNOMED-CT_Concept_Name  Name of matching SNOMED-CT concept
 SNOMED-CT_Concept_Code  Concept Code of matching SNOMED-CT concept
 SNOMED-CT_Concept_Class  Concept class of matching SNOMED-CT concept

Sample output record:
 Field  Value
 MedDRA_Concept_ID  35205180
 MedDRA_Concept_Name  Acute myocardial infarction
 MedDRA_Concept_Code  10000891
 MedDRA_Concept_Class  Preferred Term
 Relationship_ID  125
 Relationship_Name  MedDRA to SNOMED-CT equivalent (OMOP)
 SNOMED-CT_Concept_ID  312327
 SNOMED-CT_Concept_Name  Acute myocardial infarction
 SNOMED-CT_Concept_Code  57054005
 SNOMED-CT_Concept_Class  Clinical finding