After importing the MedDRA concepts into vocabulary, did you re-build the concept_hierarchy table? This query is defined here. To test it, you can just execute the portion of the query that builds the ‘Condition’ part of the concept_hiearchy for one of your SNOMED concepts to verify that the concept hierarchy records look right. If it looks like the query is pulling in the MedDRA terms, then truncate the table and rebuild it with the above query.
For your convenience, here’s the Condition treemap portion of the query you can modify to just look for the single SNOMED concept:
SELECT
snomed.concept_id,
snomed.concept_name AS snomed_concept_name,
CAST('Condition' AS VARCHAR(20)) AS treemap,
pt_to_hlt.pt_concept_name,
hlt_to_hlgt.hlt_concept_name,
hlgt_to_soc.hlgt_concept_name,
soc.concept_name AS soc_concept_name
FROM (
SELECT
concept_id,
concept_name
FROM @vocab_schema.concept
WHERE domain_id = 'Condition'
-- add a condition HERE for the specific snomed concept you wish to test.
) snomed
LEFT JOIN (
SELECT
c1.concept_id AS snomed_concept_id,
max(c2.concept_id) AS pt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.domain_id = 'Condition'
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = 'MedDRA'
GROUP BY c1.concept_id
) snomed_to_pt ON snomed.concept_id = snomed_to_pt.snomed_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS pt_concept_id,
c1.concept_name AS pt_concept_name,
max(c2.concept_id) AS hlt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = 'MedDRA'
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = 'MedDRA'
GROUP BY c1.concept_id, c1.concept_name
) pt_to_hlt ON snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS hlt_concept_id,
c1.concept_name AS hlt_concept_name,
max(c2.concept_id) AS hlgt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = 'MedDRA'
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = 'MedDRA'
GROUP BY c1.concept_id, c1.concept_name
) hlt_to_hlgt ON pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS hlgt_concept_id,
c1.concept_name AS hlgt_concept_name,
max(c2.concept_id) AS soc_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = 'MedDRA'
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = 'MedDRA'
GROUP BY c1.concept_id, c1.concept_name
) hlgt_to_soc ON hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id
LEFT JOIN @vocab_schema.concept soc ON hlgt_to_soc.soc_concept_id = soc.concept_id;
