PheKnowVec_Source_Code_Query

source_code_query.sql

-- Query Template: find exact matches between source vocabulary code strings and an OMOP standard terminology concept code 
SELECT DISTINCT
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
CONCAT('"', c1.concept_code, '"') AS standard_code,
c1.concept_name AS standard_name,
c1.domain_id AS standard_domain,
c1.vocabulary_id AS standard_vocabulary

FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r
ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1
ON r.concept_id_2 = c1.concept_id

WHERE 
r.relationship_id IN ("Maps to", "Brand name of")
AND c1.standard_concept = "S"
AND c.concept_code IN ({0})
AND c.vocabulary_id IN ({1})
AND c1.vocabulary_id IN ({2})
ORDER BY c.concept_code;