PheKnowVec_Standard_Code_Desc_Query

standard_code_desc_query.sql

-- Query Template: find descendants of OMOP standard terminology concepts
SELECT DISTINCT
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_name AS standard_name,
c.domain_id AS standard_domain,
c.vocabulary_id AS standard_vocabulary,
CONCAT('"', dc.concept_code, '"') AS desc_standard_code,
dc.concept_name AS desc_standard_name,
dc.domain_id AS desc_standard_domain,
dc.vocabulary_id AS desc_standard_vocabulary

FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_ancestor ca
ON ca.ancestor_concept_id = c.concept_id
JOIN CHCO_DeID_Oct2018.concept dc ON ca.descendant_concept_id = dc.concept_id

WHERE 
c.standard_concept = "S"
AND dc.standard_concept = 'S'
AND c.concept_code != dc.concept_code
AND c.vocabulary_id IN ({0})
AND c.concept_code IN ({1})

ORDER BY c.concept_code, dc.concept_code;