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

# Medication Prescribed for Pain

## Synopsis

This query is a variation of the [Medication Summary example](/nlp/database/cookbook/relation-queries/medication/summary). In this query, we filter the result set returned for only those medications in RxNorm, where the indication included the word `pain`.

## Processing Requirements

This query assumes that at least the following `emtellipro-db-client` processing options were enabled:

* `--feature medication-relations, rxnorm-ontology`
* `--store-sections-and-sentences`

For this query, Clinical notes were processed with the following document type:

* `--category Clinical`
* `--subcategory generic` (or more specific report types such as Discharge Summary, SOAP Note, CCD as applicable)

## Clinical Context

Sometimes, clinicians will indicate `why` a medication has been prescribed (e.g. `for pain`); this is frequently used when the frequency is `pro re nata` (`prn`, as needed), but can also be written for clarity or when a drug may have multiple possible indications.

This query is similar to the [previous](/nlp/database/cookbook/relation-queries/medication/summary) medication query, except it only matches medications coded against RxNorm (rather than RxNorm and SNOMED) and adds a filtering expression, to identify where the indication for the medication was explicitly stated as being `pain`.

## SQL query

#### PostgreSQL

```sql
WITH prescription AS (
    SELECT DISTINCT(mr.id) as mr_id,
                    fe.id as fe_id,
                    e.document_id as doc_id,
                    c.description as description,
                    c.ontology as ontology,
                    c.concept_id as concept_id
    FROM medicationrelation mr
        -- get drug name
        JOIN entity e ON mr.drug_entity_id = e.id
        JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found'
        JOIN foundentityconcept fec on fe.id = fec.found_entity_id
        JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND c.ontology='rxnorm'
    ),
    indication AS (
        SELECT DISTINCT(mri.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id)                                        AS id,
                        string_agg(fe.text, ', ')                                AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id)                                            AS id,
                        string_agg(fe.text, ', ')                                      AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id)                                       AS id,
                        string_agg(fe.text, ', ')                                 AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id)                                              AS id,
                        string_agg(fe.text, ', ')                                      AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
        SELECT DISTINCT(mrdt.medication_id)                                              AS id,
                        string_agg(fe.text, ', ')                                      AS dates
        FROM medicationrelationdatetime mrdt
                JOIN entity e ON mrdt.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdt.medication_id
    )
SELECT  prescription.mr_id,
        -- enable if documentmetadata is populated
        -- dm.chartdate,
        -- dm.subject_id,
        prescription.description,
        prescription.ontology,
        prescription.concept_id,
        indication.indications,
        dose.dosages,
        frequency.frequencies,
        quantity.quantities,
        modifier.modifiers,
        duration.durations,
        necessity.as_necessary,
        date_time.dates,
        sl.text as sentence
    FROM prescription
    LEFT JOIN indication ON prescription.mr_id = indication.id
    LEFT JOIN dose ON prescription.mr_id = dose.id
    LEFT JOIN frequency ON prescription.mr_id = frequency.id
    LEFT JOIN quantity ON prescription.mr_id = quantity.id
    LEFT JOIN modifier ON prescription.mr_id = modifier.id
    LEFT JOIN duration ON prescription.mr_id = duration.id
    LEFT JOIN necessity ON prescription.mr_id = necessity.id
    LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
    JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
    JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
    JOIN sentencelocation sl on l.location_id = sl.id
    -- Get metadata
    -- JOIN document d on d.id = l.document_id
    -- JOIN documentmetadata dm on d.id = dm.document_id
WHERE indication.indications iLIKE '%pain%'
ORDER BY prescription.mr_id ASC;
```

#### MS SQL Server

```sql
WITH prescription AS (
        SELECT DISTINCT(mr.id) as mr_id,
                        fe.id as fe_id,
                        e.document_id as doc_id,
                        c.description as description,
                        c.ontology as ontology,
                        c.concept_id as concept_id
        FROM medicationrelation mr
            -- get drug name
            JOIN entity e on mr.drug_entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found'
            JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
            JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology AND c.ontology='rxnorm'),
    indication AS (
        SELECT DISTINCT(mri.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id)                                      AS id,
                        string_agg(fe.text, ', ')                              AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id) AS id,
                STRING_AGG(fe.text, ', ') AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
        SELECT DISTINCT(mrdt.medication_id) AS id,
                    STRING_AGG(fe.text, ', ') AS dates
        FROM medicationrelationdatetime mrdt
                JOIN entity e ON mrdt.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdt.medication_id
        )
SELECT  prescription.mr_id,
        -- enable if documentmetadata is populated
        -- dm.chartdate,
        -- dm.subject_id,
        prescription.description,
        prescription.ontology,
        prescription.concept_id,
        indication.indications,
        dose.dosages,
        frequency.frequencies,
        quantity.quantities,
        modifier.modifiers,
        duration.durations,
        necessity.as_necessary,
        date_time.dates,
        sl.text as sentence
    FROM prescription
    LEFT JOIN indication ON prescription.mr_id = indication.id
    LEFT JOIN dose ON prescription.mr_id = dose.id
    LEFT JOIN frequency ON prescription.mr_id = frequency.id
    LEFT JOIN quantity ON prescription.mr_id = quantity.id
    LEFT JOIN modifier ON prescription.mr_id = modifier.id
    LEFT JOIN duration ON prescription.mr_id = duration.id
    LEFT JOIN necessity ON prescription.mr_id = necessity.id
    LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
    JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
    JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
    JOIN sentencelocation sl on l.location_id = sl.id
    -- Get metadata
    -- JOIN document d on d.id = l.document_id
    -- JOIN documentmetadata dm on d.id = dm.document_id
WHERE indication.indications iLIKE '%pain%'
ORDER BY prescription.mr_id ASC;
```

#### Snowflake

```sql
WITH  distinct_concepts AS (
      SELECT distinct * FROM concept c
     ),
     prescription AS (
        SELECT DISTINCT(mr.id) as mr_id,
                        fe.id as fe_id,
                        e.document_id as doc_id,
                        c.description as description,
                        c.ontology as ontology,
                        c.concept_id as concept_id
        FROM medicationrelation mr
            -- get drug name
            JOIN entity e ON mr.drug_entity_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id AND e.type_='found'
            JOIN foundentityconcept fec on fe.id = fec.found_entity_id
            JOIN distinct_concepts c on fec.concept_id = c.concept_id and 
                 fec.concept_ontology = c.ontology AND c.ontology='rxnorm'
    ),
    indication AS (
        SELECT DISTINCT(mri.medication_id) AS id,
                listagg(fe.text, ', ') AS indications
        FROM medicationrelationindication mri
                JOIN entity e ON mri.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mri.medication_id
    ),
    frequency AS (
        SELECT DISTINCT(mrf.medication_id) AS id,
                        listagg(fe.text, ', ') AS frequencies
        FROM medicationrelationfrequency mrf
                JOIN entity e ON mrf.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrf.medication_id
    ),
    dose AS (
        SELECT DISTINCT(mrd.medication_id) AS id,
                listagg(fe.text, ', ') AS dosages
        FROM medicationrelationdosage mrd
                JOIN entity e ON mrd.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrd.medication_id
    ),
    quantity AS (
        SELECT DISTINCT(mrq.medication_id) AS id,
                listagg(fe.text, ', ') AS quantities
        FROM medicationrelationquantity mrq
                JOIN entity e ON mrq.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrq.medication_id
    ),
    duration AS (
        SELECT DISTINCT(mrdur.medication_id) AS id,
                listagg(fe.text, ', ') AS durations
        FROM medicationrelationduration mrdur
                JOIN entity e ON mrdur.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdur.medication_id
    ),
    modifier AS (
        SELECT DISTINCT(mrmod.medication_id) AS id,
                listagg(fe.text, ', ') WITHIN GROUP (ORDER BY mrmod.medication_id) AS modifiers
        FROM medicationrelationmodifier mrmod
                JOIN entity e ON mrmod.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrmod.medication_id
    ),
    necessity AS (
        SELECT DISTINCT(mrn.medication_id) AS id,
                listagg(fe.text, ', ') WITHIN GROUP (ORDER BY mrn.medication_id) AS as_necessary
        FROM medicationrelationnecessity mrn
                JOIN entity e ON mrn.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrn.medication_id
    ),
    date_time AS (
        SELECT DISTINCT(mrdt.medication_id) AS id,
                listagg(fe.text, ', ') WITHIN GROUP (ORDER BY mrdt.medication_id) AS dates
        FROM edicationrelationdatetime mrdt
                JOIN entity e ON mrdt.entity_id = e.id
                JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
        GROUP BY mrdt.medication_id
    ) 
SELECT  prescription.mr_id,
        -- enable if documentmetadata is populated
        -- dm.chartdate,
        -- dm.subject_id,
        prescription.description,
        prescription.ontology,
        prescription.concept_id,
        indication.indications,
        dose.dosages,
        frequency.frequencies,
        quantity.quantities,
        modifier.modifiers,
        duration.durations,
        necessity.as_necessary,
        date_time.dates,
        sl.text as sentence
    FROM prescription
    LEFT JOIN indication ON prescription.mr_id = indication.id
    LEFT JOIN dose ON prescription.mr_id = dose.id
    LEFT JOIN frequency ON prescription.mr_id = frequency.id
    LEFT JOIN quantity ON prescription.mr_id = quantity.id
    LEFT JOIN modifier ON prescription.mr_id = modifier.id
    LEFT JOIN duration ON prescription.mr_id = duration.id
    LEFT JOIN necessity ON prescription.mr_id = necessity.id
    LEFT JOIN date_time ON prescription.mr_id = date_time.id
    -- Get sentence text
    JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
    JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
    JOIN sentencelocation sl on l.location_id = sl.id
    -- Get metadata
    -- JOIN document d on d.id = l.document_id
    -- JOIN documentmetadata dm on d.id = dm.document_id
WHERE indication.indications iLIKE '%pain%'
ORDER BY prescription.mr_id ASC;
```

| mr_id | description                             | ontology | concept_id | indications  | dosages          | frequencies | quantities | modifiers | durations | as_necessary | dates | sentence                                                                                              |
| ----- | --------------------------------------- | -------- | ---------- | ------------ | ---------------- | ----------- | ---------- | --------- | --------- | ------------ | ----- | ----------------------------------------------------------------------------------------------------- |
| 20    | morphine (Pharmacologic Substance)      | rxnorm   | 7052       | pain control | —                | —           | —          | —         | —         | —            | —     | She was started on IV fluids as well as morphine for pain control and gravol for nausea and vomiting. |
| 109   | Tylenol (Pharmacologic Substance)       | rxnorm   | 202433     | pain         | —                | Q6H         | 1-2        | —         | —         | PRN          | —     | Extra strength tylenol 1-2 tabs PO Q6H PRN pain                                                       |
| 117   | Percocet (Pharmacologic Substance)      | rxnorm   | 42844      | pain         | 5/325 mg         | q.4-6h.     | one        | —         | —         | as needed    | —     | Percocet 5/325 mg one tablet p.o. q.4-6h. as needed for pain.                                         |
| 142   | acetaminophen (Pharmacologic Substance) | rxnorm   | 161        | pain         | 325 mg to 650 mg | q.4-6h.     | —          | —         | —         | as needed    | —     | Acetaminophen 325 mg to 650 mg p.o. q.4-6h. as needed (for pain).                                     |
| 151   | Percocet (Pharmacologic Substance)      | rxnorm   | 42844      | pain         | 5/325 mg         | q.4-6h.     | one        | —         | —         | as needed    | —     | Percocet 5/325 mg one tablet p.o. q.4-6h. as needed for pain.                                         |

## Next steps

In addition to using the LIKE operator to find terms containing the word `pain`, you can retrieve pain mentions using a number of different strategies:

1. Search those entities in the **indications** argument that map to pain concepts using Snomed concept hierarchies. This allows for a more generalized search for related terms.
2. If you are using PostgreSQL, you can search the sentences from which the **indication** entity was extracted for pain mentions using full-text search. In PostgreSQL, Output Database schema sets up indexes for store sentence and section text columns. See [Query: Retrieve a Sentence using Full-text Search](/nlp/database/schema/core/document-tables#query-retrieve-a-sentence-using-full-text-search).

## Related links

* [Entity-level Tables](/nlp/database/schema/core/entity-tables)
* [Medication Relations](/nlp/database/schema/relations/medication)