Skip to content

Instantly share code, notes, and snippets.

@callahantiff

callahantiff/code_count.sql

Last active August 14, 2019 04:52
Show Gist options
  • Save callahantiff/523cb25f5e5c72a3361f4498a4fabb9b to your computer and use it in GitHub Desktop.
Save callahantiff/523cb25f5e5c72a3361f4498a4fabb9b to your computer and use it in GitHub Desktop.
Download ZIP
PheKnowVec Code Set Test Queries
Raw
code_count.sql
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS standard_code,
c.concept_name AS standard_name,
c.domain_id AS standard_domain,
c.vocabulary_id AS standard_vocabulary,
COUNT(occ.{count_concept}_id) AS occ_count
FROM
{database}.concept c
JOIN {database}.{count_concept} occ ON occ.{concept}_concept_id = c.concept_id
WHERE
c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({vocabulary_id})
AND c.domain_id IN ({domain_id})
GROUP BY
standard_code,
standard_name,
standard_domain,
standard_vocabulary
ORDER BY
standard_code,
standard_vocabulary;
Raw
concept_synonym.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
ORDER BY
source_string,
source_code,
source_vocabulary;
Raw
concept_synonym_child.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_code,
c.concept_id,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', d.concept_code, '"') AS source_code,
d.concept_id AS source_concept_id,
d.concept_name AS source_name,
d.domain_id AS source_domain,
d.vocabulary_id AS source_vocabulary
FROM
{database}.concept_ancestor ca
JOIN (
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
c.concept_id
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
) syn ON ca.ancestor_concept_id = syn.concept_id
JOIN {database}.concept d on ca.descendant_concept_id = d.concept_id
WHERE
d.domain_id IN ({domain_id})
AND syn.source_vocabulary = d.vocabulary_id
AND ca.min_levels_of_separation IN (0, 1)
)
)
ORDER BY
source_string,
source_code,
source_vocabulary;
Raw
concept_synonym_desc.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_code,
c.concept_id,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', d.concept_code, '"') AS source_code,
d.concept_id AS source_concept_id,
d.concept_name AS source_name,
d.domain_id AS source_domain,
d.vocabulary_id AS source_vocabulary
FROM
{database}.concept_ancestor ca
JOIN (
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
c.concept_id
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT cs.concept_id,
cs.concept_synonym_name,
CASE
{concept_synonym}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept_synonym cs
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
) syn ON ca.ancestor_concept_id = syn.concept_id
JOIN {database}.concept d on ca.descendant_concept_id = d.concept_id
WHERE
d.domain_id IN ({domain_id})
AND syn.source_vocabulary = d.vocabulary_id
)
)
ORDER BY
source_string,
source_code,
source_vocabulary;
Raw
source_code_child.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_code,
c.concept_id,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', d.concept_code, '"') AS source_code,
d.concept_id AS source_concept_id,
d.concept_name AS source_name,
d.domain_id AS source_domain,
d.vocabulary_id AS source_vocabulary
FROM
{database}.concept_ancestor ca
JOIN (
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
c.concept_id
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
) syn ON ca.ancestor_concept_id = syn.concept_id
JOIN {database}.concept d on ca.descendant_concept_id = d.concept_id
WHERE
d.domain_id IN ({domain_id})
AND syn.source_vocabulary = d.vocabulary_id
AND ca.min_levels_of_separation IN (0, 1)
)
)
ORDER BY
source_string,
source_code,
source_vocabulary;
Raw
source_code_desc.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_code,
c.concept_id,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
)
UNION ALL
(
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', d.concept_code, '"') AS source_code,
d.concept_id AS source_concept_id,
d.concept_name AS source_name,
d.domain_id AS source_domain,
d.vocabulary_id AS source_vocabulary
FROM
{database}.concept_ancestor ca
JOIN (
SELECT
DISTINCT syn.source_string AS source_string,
CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
c.concept_id
FROM
{database}.concept c
JOIN (
SELECT
concept_id,
source_string
FROM
(
SELECT
DISTINCT c.concept_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING"
) syn ON syn.concept_id = c.concept_id
WHERE
c.domain_id IN ({domain_id})
) syn ON ca.ancestor_concept_id = syn.concept_id
JOIN {database}.concept d on ca.descendant_concept_id = d.concept_id
WHERE
d.domain_id IN ({domain_id})
AND syn.source_vocabulary = d.vocabulary_id
)
)
ORDER BY
source_string,
source_code,
source_vocabulary;
Raw
source_string_match.sql
SELECT
DISTINCT source_string,
CONCAT('"', concept_code, '"') AS source_code,
concept_id AS source_concept_id,
concept_name AS source_name,
domain_id AS source_domain,
vocabulary_id AS source_vocabulary
FROM
(
SELECT
DISTINCT c.concept_name,
c.concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
{concept_name}
ELSE "NOT A MATCH TO SOURCE STRING" END AS source_string
FROM
{database}.concept c
WHERE
c.domain_id IN ({domain_id})
)
WHERE
source_string != "NOT A MATCH TO SOURCE STRING";
Raw
standard_concept_synonym.sql
SELECT
DISTINCT *
FROM
(
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
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('"', cs.concept_code, '"') AS standard_code,
cs.concept_name AS standard_name,
cs.domain_id AS standard_domain,
cs.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
)
ORDER BY
source_code,
source_name,
source_vocabulary,
standard_code,
standard_name;
Raw
standard_concept_synonym_child.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
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('"', cs.concept_code, '"') AS standard_code,
cs.concept_name AS standard_name,
cs.domain_id AS standard_domain,
cs.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
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('"', cs.concept_code, '"') AS standard_code,
cs.concept_name AS standard_name,
cs.domain_id AS standard_domain,
cs.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
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('"', d.concept_code, '"') AS standard_code,
d.concept_name AS standard_name,
d.domain_id AS standard_domain,
d.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
JOIN CHCO_DeID_Oct2018.concept_ancestor ca on ca.ancestor_concept_id = cs.concept_id
JOIN CHCO_DeID_Oct2018.concept d on d.concept_id = ca.descendant_concept_id
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
AND d.domain_id IN ({domain_id})
AND cs.vocabulary_id = d.vocabulary_id
AND ca.min_levels_of_separation IN (0, 1)
)
)
ORDER BY
source_code,
source_name,
source_vocabulary,
standard_code,
standard_name;
Raw
standard_concept_synonym_desc.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT *
FROM
(
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
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('"', cs.concept_code, '"') AS standard_code,
cs.concept_name AS standard_name,
cs.domain_id AS standard_domain,
cs.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
)
)
UNION ALL
(
SELECT
DISTINCT *
FROM
(
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('"', cs.concept_code, '"') AS standard_code,
cs.concept_name AS standard_name,
cs.domain_id AS standard_domain,
cs.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
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('"', d.concept_code, '"') AS standard_code,
d.concept_name AS standard_name,
d.domain_id AS standard_domain,
d.vocabulary_id AS standard_vocabulary
FROM
CHCO_DeID_Oct2018.concept c,
CHCO_DeID_Oct2018.concept_synonym s,
CHCO_DeID_Oct2018.concept cs
JOIN CHCO_DeID_Oct2018.concept_ancestor ca on ca.ancestor_concept_id = cs.concept_id
JOIN CHCO_DeID_Oct2018.concept d on d.concept_id = ca.descendant_concept_id
WHERE
c.concept_id IN (
SELECT
DISTINCT c1.concept_id
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")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
AND c.concept_id = s.concept_id
AND s.concept_synonym_name = cs.concept_name
AND cs.vocabulary_id IN ({standard_vocabulary_id})
AND d.domain_id IN ({domain_id})
AND cs.vocabulary_id = d.vocabulary_id
)
)
ORDER BY
source_code,
source_name,
source_vocabulary,
standard_code,
standard_name;
Raw
standard_terms.sql
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_name AS source_name,
c.concept_id AS source_concept_id,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
CONCAT('"', c1.concept_code, '"') AS standard_code,
c1.concept_id AS standard_concept_id,
c1.concept_name AS standard_name,
c1.domain_id AS standard_domain,
c1.vocabulary_id AS standard_vocabulary
FROM
{database}.concept c
JOIN {database}.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN {database}.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE
r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
ORDER BY
source_code,
source_vocabulary,
standard_code;
Raw
standard_terms_child.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
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_id AS standard_concept_id,
c1.concept_name AS standard_name,
c1.domain_id AS standard_domain,
c1.vocabulary_id AS standard_vocabulary
FROM
{database}.concept c
JOIN {database}.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN {database}.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE
r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
CONCAT('"', d.concept_code, '"') AS standard_code,
d.concept_id AS standard_concept_id,
d.concept_name AS standard_name,
d.domain_id AS standard_domain,
d.vocabulary_id AS standard_vocabulary
FROM
{database}.concept c
JOIN {database}.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN {database}.concept c1 ON r.concept_id_2 = c1.concept_id
JOIN {database}.concept_ancestor ca ON ca.ancestor_concept_id = c1.concept_id
JOIN {database}.concept d ON ca.descendant_concept_id = d.concept_id
WHERE
r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
AND c1.vocabulary_id = d.vocabulary_id
AND ca.min_levels_of_separation IN (0, 1)
)
ORDER BY
source_code,
source_name,
source_vocabulary,
standard_code,
standard_vocabulary;
Raw
standard_terms_desc.sql
SELECT
DISTINCT *
FROM
(
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
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_id AS standard_concept_id,
c1.concept_name AS standard_name,
c1.domain_id AS standard_domain,
c1.vocabulary_id AS standard_vocabulary
FROM
{database}.concept c
JOIN {database}.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN {database}.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE
r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
)
UNION ALL
(
SELECT
DISTINCT CONCAT('"', c.concept_code, '"') AS source_code,
c.concept_id AS source_concept_id,
c.concept_name AS source_name,
c.domain_id AS source_domain,
c.vocabulary_id AS source_vocabulary,
CONCAT('"', d.concept_code, '"') AS standard_code,
d.concept_id AS standard_concept_id,
d.concept_name AS standard_name,
d.domain_id AS standard_domain,
d.vocabulary_id AS standard_vocabulary
FROM
{database}.concept c
JOIN {database}.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN {database}.concept c1 ON r.concept_id_2 = c1.concept_id
JOIN {database}.concept_ancestor ca ON ca.ancestor_concept_id = c1.concept_id
JOIN {database}.concept d ON ca.descendant_concept_id = d.concept_id
WHERE
r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ({concept_codes})
AND c.vocabulary_id IN ({source_vocabulary_id})
AND c.domain_id IN ({domain_id})
AND c1.vocabulary_id IN ({standard_vocabulary_id})
AND c1.vocabulary_id = d.vocabulary_id
)
ORDER BY
source_code,
source_name,
source_vocabulary,
standard_code,
standard_vocabulary;
@callahantiff
Copy link
Author

Queries constructed by modifying OMOP CDM queries

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment