OHDSI Home | Forums | Wiki | Github

Concept_synonym where concept_relationship mapping fails

Vocabulary Users
#1

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';

Edit:
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.

t