> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.emtelligent.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.emtelligent.com/_mcp/server.

# Cardiovascular Disease Symptoms

## Synopsis

This query uses recursion and the Snomed CT concept hierarchy to find all the child concepts for cardiovascular disease. Using the Experiencer relaton, this query restricts the results to only those mentions affecting the patient herself.

## Processing Requirements

This query assumes that at least the following processing options were enabled:

* `--feature experiencer-relation, snomed-ontology`
* `--store-sections-and-sentences`

For this query, Clinical notes containing `HISTORY OF PRESENT ILLNESS` section were processed with the following document type:

* `--category Clinical`
* `--subcategory generic`

Additionally, this query requires the snomed relationships table. This table can be imported into the Output Database following the instructions provided [here](/nlp/database/schema/extensions/snomed-hierarchies).

## Clinical Context

Due to the complexity of human biology, there are many disease states; SNOMED has encoded many hundreds of thousands of concepts referring to diseases organizing them hierarchically. For example, the concept `49601007 |Disorder of cardiovascular system (disorder)|` is a `parent` concept to 52 `child` concepts, one of which is `56265001 |Heart disease (disorder)|`, which itself is a parent concept to nearly 12,000 additional child concepts when each branch in the SNOMED hierarchy below `56265001` is followed to the leaf concepts.

As there are many medical synonyms, and subtypes of disease, and the NLP API will always try to identify mentions in clinical text with the **highest precision** possible - i.e. emtellipro tries match the most specific disorder subtype by returning the longest match. This is a good thing, because if a clinician writes that a patient has `left posterior fascicular block`, the NLP API will code this to `62026008 |Left posterior fascicular block (disorder)|` preserving as much information from the text as possible. However, this behaviour makes finding general disease references more challenging.

If a clinician or researcher wants to know if a patient has any type of heart disease, it would be impractical to write a SQL query that specifies all 12,000 concepts related to **heart disease**. Instead, they would rather write a query for a single concept in the SNOMED hierarchy and find matches for that concept and its children concepts that are in the SNOMED hierarchy.

emtelligent has enabled this functionality by allowing users to add the SNOMED relationship table (**sct_relationship**) to their Output Databases. When the **sct_relationship** table has been added to an Output Database, the database then has 'knowledge' of the SNOMED hierarchy, and can perform hierarchical queries to identify any concept mentions at and below a given level in the hierarchy. The query below uses this add-on table and recursive search functionality in SQL to perform this very broad type of search by querying for all child concepts for:

* `49601007 |Disorder of cardiovascular system (disorder)|`

## SQL query

#### SQL

```sql
WITH RECURSIVE
    subtypeids (sourceId) AS (
        -- This recursive CTE returns rows containing subtype SCTIDs
        SELECT r.sourceId
        FROM sct_relationship r
        WHERE r.destinationId IN ('49601007') -- Starting concepts (find all their children recursively) 
            AND r.typeId = '116680003'
            AND active = 1
        UNION ALL
        SELECT r.sourceId
        FROM sct_relationship r
                INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
        WHERE r.active = 1
            AND r.typeId = '116680003'
    ),
    sctids (sourceId) AS (
        SELECT '49601007' as sourceId -- add starting concepts to get the final set of ids to find
        UNION 
        SELECT DISTINCT sourceId -- IMPORTANT: distinct is required
        FROM subtypeids
    ),
    patient_ers AS (
        SELECT er.id AS expid, e.type_ as mention, c.description as experiencer
        FROM experiencerrelation er
                JOIN entity e
                        ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
                JOIN foundentity fe
                        ON fe.id = e.entity_id
                JOIN foundentityconcept fec
                        ON fe.id = fec.found_entity_id
                JOIN concept c
                        ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
        WHERE c.ontology = 'snomed'
            AND e.type_ = 'found'
            AND c.concept_id = '116154003' -- SCTID of 'Patient (person)'
        UNION
        SELECT er.id AS expid, e.type_ as mention, ae.value as experiencer
        FROM entity e
                JOIN experiencerrelation er
                        ON er.experiencer_id = e.id
                JOIN assumedentity ae
                        ON ae.id = e.entity_id
        WHERE e.type_ = 'assumed'
            AND ae.value = 'patient'
    )
    SELECT -- dm.subject_id           as subject_id,
        -- dm.chartdate            as chartdate, 
        -- regexp_replace(d.filename, '.*/', '')              as filename,
        c.description           as fully_specified_name,
        c.concept_id,
        patient_ers.experiencer as experiencer,
        fe.text                as term,
        sl.text                 as sentence
    FROM patient_ers
            JOIN experiencerrelation er ON patient_ers.expid = er.id
            JOIN entity e ON er.experienced_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id
            JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
            JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
            JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
            JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence')
            JOIN sentencelocation sl ON sl.id = l.location_id AND (l.type_ = 'sentence')
            JOIN foundentitytype fet ON fet.found_entity_id = fe.id AND (fet.ontology LIKE 'snomed')
            JOIN document d ON d.id = l.document_id
            JOIN documentmetadata dm ON d.id = dm.document_id
    WHERE c.ontology = 'snomed'
    AND c.concept_id IN (SELECT * FROM sctids)
    AND fe.polarity = 'asserted';
```

#### Snowflake

```sql
WITH distinct_concepts AS (SELECT DISTINCT * FROM concept),
subtypeids (sourceId) AS (
    -- This recursive CTE returns rows containing subtype SCTIDs
    SELECT r.sourceId
    FROM sct_relationship r
    WHERE r.destinationId IN ('49601007') -- Starting concepts (find all their children recursively)
            AND r.typeId = '116680003'
            AND active = 1
    UNION ALL
    SELECT r.sourceId
    FROM sct_relationship r
                    INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
    WHERE r.active = 1 AND r.typeId = '116680003'
    ),
sctids (sourceId) AS (
    SELECT '49601007' as sourceId -- add starting concepts to get the final set of ids to find
    UNION
    SELECT DISTINCT sourceId -- IMPORTANT: distinct is required
    FROM subtypeids
    ),
patient_ers AS (
    SELECT er.id AS expid, e.type_ as mention, c.description as experiencer
    FROM experiencerrelation er
                    JOIN entity e
                            ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
                    JOIN foundentity fe
                            ON fe.id = e.entity_id
                    JOIN foundentityconcept fec
                            ON fe.id = fec.found_entity_id
                    JOIN distinct_concepts c
                            ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
    WHERE c.ontology = 'snomed'
            AND e.type_ = 'found'
            AND c.concept_id = '116154003' -- SCTID of 'Patient (person)'
    UNION
    SELECT er.id AS expid, e.type_ as mention, ae.value as experiencer
    FROM entity e
                    JOIN experiencerrelation er
                            ON er.experiencer_id = e.id
                    JOIN assumedentity ae
                            ON ae.id = e.entity_id
    WHERE e.type_ = 'assumed'
            AND ae.value = 'patient'
)
SELECT -- dm.subject_id           as subject_id,
       -- dm.chartdate            as chartdate,
       -- regexp_replace(d.filename, '.*/', '')              as filename,
       c.description           as fully_specified_name,
       c.concept_id,
       patient_ers.experiencer as experiencer,
       fe.text                as term,
       sl.text                 as sentence
FROM patient_ers
        JOIN experiencerrelation er ON patient_ers.expid = er.id
        JOIN entity e ON er.experienced_id = e.id
        JOIN foundentity fe ON fe.id = e.entity_id
        JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
        JOIN distinct_concepts c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
        JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
        JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence')
        JOIN sentencelocation sl ON sl.id = l.location_id AND (l.type_ = 'sentence')
        JOIN foundentitytype fet ON fet.found_entity_id = fe.id AND (fet.ontology LIKE 'snomed')
        JOIN document d ON d.id = l.document_id
        JOIN documentmetadata dm ON d.id = dm.document_id
WHERE c.ontology = 'snomed'
AND c.concept_id IN (SELECT * FROM sctids)
AND fe.polarity = 'asserted';
```

#### MS SQL Server

```sql
WITH subtypeids (sourceId) AS (
    -- This recursive CTE returns rows containing subtype SCTIDs
    SELECT r.sourceId
    FROM sct_relationship r
    WHERE r.destinationId IN (49601007) -- Starting concepts (find all their children recursively)
            AND r.typeId = '116680003'
            AND active = 1
    UNION ALL
    SELECT r.sourceId
    FROM sct_relationship r
                INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
    WHERE r.active = 1
            AND r.typeId = '116680003'
    ),
    sctids (sourceId) AS (
    SELECT '49601007' as sourceId -- add starting concepts to get the final set of ids to find
    UNION
    SELECT DISTINCT sourceId -- IMPORTANT: distinct is required
    FROM subtypeids
    ),
patient_ers AS (
SELECT er.id AS expid, e.type_ as mention, c.description as experiencer
FROM experiencerrelation er
        JOIN entity e
                ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
        JOIN foundentity fe
                ON fe.id = e.entity_id
        JOIN foundentityconcept fec
                ON fe.id = fec.found_entity_id
        JOIN concept c
                ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
WHERE c.ontology = 'snomed'
        AND e.type_ = 'found'
        AND c.concept_id = '116154003' -- SCTID of 'Patient (person)'
UNION
SELECT er.id AS expid, e.type_ as mention, ae.value as experiencer
FROM entity e
        JOIN experiencerrelation er
                ON er.experiencer_id = e.id
        JOIN assumedentity ae
                ON ae.id = e.entity_id
WHERE e.type_ = 'assumed'
        AND ae.value = 'patient'
)
SELECT -- dm.subject_id           as subject_id,
    -- dm.chartdate            as chartdate,
    -- regexp_replace(d.filename, '.*/', '')              as filename,
    c.description           as fully_specified_name,
    c.concept_id,
    patient_ers.experiencer as experiencer,
    fe.text                as term,
    sl.text                 as sentence
FROM patient_ers
            JOIN experiencerrelation er ON patient_ers.expid = er.id
            JOIN entity e ON er.experienced_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id
            JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
            JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
            JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
            JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence')
            JOIN sentencelocation sl ON sl.id = l.location_id AND (l.type_ = 'sentence')
            JOIN foundentitytype fet ON fet.found_entity_id = fe.id AND (fet.ontology LIKE 'snomed')
            JOIN document d ON d.id = l.document_id
            JOIN documentmetadata dm ON d.id = dm.document_id
WHERE c.ontology = 'snomed'
AND c.concept_id IN (SELECT * FROM sctids)
AND fe.polarity = 'asserted';
```

| fully_specified_name                                        | concept_id | experiencer | term                                                 |
| ----------------------------------------------------------- | ---------- | ----------- | ---------------------------------------------------- |
| Abdominal aortic atherosclerosis (disorder)                 | 233955003  | patient     | atherosclerotic calcification of the abdominal aorta |
| Acute myocardial infarction (disorder)                      | 57054005   | patient     | Acute myocardial infarction                          |
| Acute Q wave myocardial infarction (disorder)               | 304914007  | patient     | Acute Q wave myocardial infarction                   |
| Acute ST segment elevation myocardial infarction (disorder) | 401303003  | patient     | ST elevation (STEMI) myocardial infarction           |
| Amaurosis fugax (disorder)                                  | 88032003   | patient     | Amaurosis fugax                                      |

## Next steps

You can apply this query pattern to find related symptoms for various disease conditions or syndromes of interest.

## Related links

* [Using SNOMED CT Concept Hierarchies](/nlp/database/schema/extensions/snomed-hierarchies)
* [Entity-level Tables](/nlp/database/schema/core/entity-tables)