I’ll take the first shot at answering this. @Christian_Reich, please chime in if I’m wrong or if you would take a slightly different approach.
Two things I would consider:
- Only using valid concepts (concepts can deprecate so I usually only use the non-deprecated ones but not everyone takes the approach)
- If we know the MeSH term should be representing a condition, we may want to filter our SNOMED to ‘Clinical Finding’
Here is slightly updated code, which will pull back the complete MeSH to SNOMED map.
SELECT B.CONCEPT_ID, B.CONCEPT_NAME, b.vocabulary_id, a.CONCEPT_ID, A.CONCEPT_NAME, A.CONCEPT_CODE, a.vocabulary_id
FROM CONCEPT A, CONCEPT B, CONCEPT_RELATIONSHIP CR
WHERE A.VOCABULARY_ID = 'Mesh' AND
CR.CONCEPT_ID_1 = A.CONCEPT_ID AND
CR.CONCEPT_ID_2 = B.CONCEPT_ID AND
CR.RELATIONSHIP_ID = 'Maps to' AND
CR.INVALID_REASON IS NULL AND
B.CONCEPT_ID = CR.CONCEPT_ID_2 AND
B.VOCABULARY_ID = 'SNOMED' AND
a.INVALID_REASON IS NULL AND
b.INVALID_REASON IS NULL AND
b.CONCEPT_CLASS_ID = 'Clinical Finding'
ORDER BY B.CONCEPT_ID, a.CONCEPT_ID
Like I said, I rarely use the CONCEPT_RELATIONSHIP table so I learned something tonight with the ‘MedDRA - SNOMED eq’.
However I would make similar recommendations as I stated above.
SELECT B.CONCEPT_ID, B.CONCEPT_NAME, B.CONCEPT_CODE, b.VOCABULARY_ID, a.CONCEPT_ID, A.CONCEPT_NAME, A.CONCEPT_CODE, a.VOCABULARY_ID
FROM CONCEPT A, CONCEPT B, CONCEPT_RELATIONSHIP CR
WHERE A.VOCABULARY_ID = 'MedDRA' AND
CR.CONCEPT_ID_1 = A.CONCEPT_ID AND
CR.CONCEPT_ID_2 = B.CONCEPT_ID AND
CR.RELATIONSHIP_ID = 'MedDRA - SNOMED eq' AND
CR.INVALID_REASON IS NULL AND
B.CONCEPT_ID = CR.CONCEPT_ID_2 AND
B.VOCABULARY_ID = 'SNOMED' AND
B.INVALID_REASON IS NULL AND
b.CONCEPT_CLASS_ID = 'Clinical Finding' AND
A.INVALID_REASON IS NULL
ORDER BY B.CONCEPT_ID, a.CONCEPT_ID
Some branded drugs terminate in a Clinical Drug, some only terminate in a ingredient (e.g. 19000951-Diazepam 5 MG Rectal Suppository [Valium]), and some terminate no where (e.g. 19034360-Nandrolone 50 MG/ML Injectable Solution [Hybolin-Improved]). This query will attempt to get a Clinical Drug and if that fails try an ingredient.
Could we just skip the Clinical Drug step all together and go straight to ingredient?
WITH CTE_BRANDED_TO_CLINICAL_DRUG AS (
SELECT c2.CONCEPT_ID AS CONCEPT_ID, c2.CONCEPT_NAME AS CONCEPT_NAME, c2.CONCEPT_CODE AS CONCEPT_CODE, c2.VOCABULARY_ID AS VOCAB_ID,
C.CONCEPT_ID AS BD_CONCEPT_ID, C.CONCEPT_NAME AS BD_CONCEPT_NAME, C.CONCEPT_CODE AS BD_CONCEPT_CODE, C.VOCABULARY_ID AS BD_VOCAB_ID
FROM CONCEPT c
JOIN CONCEPT_ANCESTOR ca
ON ca.descendant_concept_id = c.CONCEPT_ID
JOIN CONCEPT c2
ON c2.CONCEPT_ID = ca.ANCESTOR_CONCEPT_ID
AND c2.vocabulary_id = 'RxNorm'
AND c2.concept_class_id = 'Clinical Drug'
AND c2.invalid_reason IS NULL
WHERE c.VOCABULARY_ID = 'RxNorm'
AND c.concept_class_id IN ( 'Branded Drug', 'Branded Pack')
AND c.invalid_reason IS NULL
),
CTE_BRANDED_TO_INGREDIENT AS (
SELECT c2.CONCEPT_ID AS CONCEPT_ID, c2.CONCEPT_NAME AS CONCEPT_NAME, c2.CONCEPT_CODE AS CONCEPT_CODE, c2.VOCABULARY_ID AS VOCAB_ID,
C.CONCEPT_ID AS BD_CONCEPT_ID, C.CONCEPT_NAME AS BD_CONCEPT_NAME, C.CONCEPT_CODE AS BD_CONCEPT_CODE, C.VOCABULARY_ID AS BD_VOCAB_ID
FROM CONCEPT c
JOIN CONCEPT_ANCESTOR ca
ON ca.descendant_concept_id = c.CONCEPT_ID
JOIN CONCEPT c2
ON c2.CONCEPT_ID = ca.ANCESTOR_CONCEPT_ID
AND c2.vocabulary_id = 'RxNorm'
AND c2.concept_class_id = 'Ingredient'
AND c2.invalid_reason IS NULL
WHERE c.VOCABULARY_ID = 'RxNorm'
AND c.concept_class_id IN ( 'Branded Drug', 'Branded Pack')
AND c.invalid_reason IS NULL
AND c.CONCEPT_ID NOT IN (
SELECT DISTINCT BD_CONCEPT_ID FROM CTE_BRANDED_TO_CLINICAL_DRUG
)
)
SELECT *
FROM CTE_BRANDED_TO_CLINICAL_DRUG
UNION
SELECT *
FROM CTE_BRANDED_TO_INGREDIENT