I am using this to find mappings where I am not finding mappings via concept_relationship; think of the temp table #cc as a listing of non-standard codes that have to be mapped via additional SQL and or bridge tables.
Is anyone using such a strategy and has it worked well? Is there anything I have missed in these queries?
SELECT ax.old_concept_code FROM ( SELECT DISTINCT x.old_concept_code/*,c.concept_code, cx.concept_name AS old_name, c.concept_name, cx.concept_id AS old_id, c.concept_id, cx.vocabulary_id AS old_vocab, c.vocabulary_id, cx.domain_id AS old_domain, c.domain_id, cx.concept_class_id AS old_class, c.concept_class_id*/ FROM #cc (NOLOCK) x INNER JOIN saftinet.dbo.concept (NOLOCK) cx ON x.old_concept_code = cx.concept_code AND ISNULL(cx.standard_concept,'') <> 'S' AND cx.vocabulary_id <> 'Nebraska Lexicon' INNER JOIN concept_synonym (NOLOCK) syn ON cx.concept_id = syn.concept_id INNER JOIN concept_synonym (NOLOCK) syn2 ON syn.concept_synonym_name = syn2.concept_synonym_name AND syn.concept_id <> syn2.concept_id INNER JOIN saftinet.dbo.concept (NOLOCK) c ON syn2.concept_id = c.concept_id AND cx.domain_id = c.domain_id AND c.vocabulary_id IN ('SNOMED','OMOP Extension') AND c.standard_concept = 'S' AND c.invalid_reason = '' )ax EXCEPT SELECT DISTINCT x2.old_concept_code FROM #cc x2 INNER JOIN saftinet.dbo.concept (NOLOCK) c3 ON x2.old_concept_code = c3.concept_code INNER JOIN saftinet.dbo.concept_relationship (NOLOCK) cr ON c3.concept_id = cr.concept_id_1 AND cr.relationship_id IN ('Maps to','is a') INNER JOIN saftinet.dbo.concept (NOLOCK) c5 ON cr.concept_id_2 = c5.concept_id WHERE c3.vocabulary_id <> 'Nebraska Lexicon' AND c3.standard_concept <> 'S' AND c5.vocabulary_id IN ('SNOMED','OMOP Extension') AND c5.standard_concept = 'S';
My single hop query, looking at ‘is a’ mapping, will not work always. I had to modify this by using recursive ‘is a’ queries into temp tables. I miss Postgres.