# Composite Views

The section [Enabling Versioned Views](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.6-enabling-versioned-views.md) considered the value of views of individual database tables. The objective of these views was to facilitate access to a range of useful snapshot and delta views. This section introduces the idea of defining composite views that access interrelated sets of data from different release files. The objective of composite views is to make it easier to access appropriate sets of data.

{% hint style="info" %}
**Example**

Apart from the concept identifier, the concept table contains no useful human-readable data about a concept. Therefore, simply selecting data from a snapshot view of the concept table is unlikely to be useful.

To display appropriate human-readable information about a concept information is required from two other tables:

* The human-readable information about a concept is in the description table or view (e.g. snap\_description)
* Information about which descriptions are preferred or acceptable in given language data is in a language refset table or view (e.g. snap\_refset\_language).

To display human-readable information about the way a concept is defined data is required from three other tables:

* The defining relationships are in the relationships table or view (e.g. snap\_relationship)
* Human-readable display of the type and value specified in the relationship requires data from the description table or view (e.g. snap\_description)
* Information about which descriptions are preferred or acceptable in given language data is in a language refset table or view (e.g. snap\_refset\_language).
  {% endhint %}

The following subsections describe some general characteristics of composite views and introduce some of the composite views included in the SNOMED CT example database.

## General Characteristics of Composite Views

### Data Sources for Composite Views

Composite views need to get data from snapshot views. In most cases the requirements will be met by use of the current [snapshot view](#user-content-fn-1)[^1] of each of the relevant tables. However, when reviewing past data the relevant retrospective views will need to be accessed. [For this reason, consideration should be given to creating similar composite views for each supported snapshot view](#user-content-fn-2)[^2].

Most composite views should to gather all the required data from the table views for the same snapshot as illustrated in Table 1.

**Table 1: Source Views for Data in Composite Views of Different Snapshots**

<table data-header-hidden><thead><tr><th></th><th width="198.22265625"></th><th width="195.21484375"></th><th width="203.19140625"></th></tr></thead><tbody><tr><td><strong>Composite View</strong></td><td><strong>snap_pref</strong></td><td><strong>snap1_pref</strong></td><td><strong>snap2_pref</strong></td></tr><tr><td><strong>Description View</strong></td><td>snap_description</td><td>snap1_description</td><td>snap2_description</td></tr><tr><td><strong>Language Refset View</strong></td><td>snap_refset_language</td><td>snap1_refset_language</td><td>snap2_refset_language</td></tr></tbody></table>

Composite views may themselves gather data from other composite views. For example as shown in Table 2 gets preferred term data from the preferred term composite views shown above.

**Table 2: Composite View Including Data from Another Composite View**

| **Composite View**      | **snap\_rel\_pref** | **snap1\_rel\_pref** | **snap2\_rel\_pref** |
| ----------------------- | ------------------- | -------------------- | -------------------- |
| **Concept View**        | snap\_concept       | snap1\_concept       | snap2\_concept       |
| **Relationship View**   | snap\_relationship  | snap1\_relationship  | snap2\_relationship  |
| **Preferred Term View** | snap\_pref          | snap1\_pref          | snap2\_pref          |

Composite views designed to support review of changes may gather data from different views as illustrated in Table 3.

**Table 3: Source Views for Data in Historical Composite Views of Different Delta Views**

<table data-header-hidden><thead><tr><th></th><th width="233.91796875"></th><th width="228.58203125"></th><th width="222.69140625"></th></tr></thead><tbody><tr><td><strong>Composite View</strong></td><td><strong>delta_inactive_concepts</strong></td><td><strong>delta1_inactive_concepts</strong></td><td><strong>delta2_inactive_concepts</strong></td></tr><tr><td><strong>Concept View</strong></td><td>delta_concept</td><td>delta1_concept</td><td>delta2_concept</td></tr><tr><td><strong>Association Refset View</strong></td><td>snap_refset_association</td><td>snap_refset_association</td><td>snap_refset_association</td></tr><tr><td><strong>Attribute Value Refset View</strong></td><td>snap_refset_attributevalue</td><td>snap_refset_attributevalue</td><td>snap_refset_attributevalue</td></tr><tr><td><strong>Preferred Term View</strong></td><td>snap_pref</td><td>snap_pref</td><td>snap_pref</td></tr><tr><td><a data-footnote-ref href="#user-content-fn-3"><strong>Fully Specified Name View</strong></a></td><td>snap_fsn</td><td>snap_fsn</td><td>snap_fsn</td></tr></tbody></table>

### Representation of Composite Views

Composite views should be represented as database views rather than a physical database tables. Composite views denormalize data by combining the same data in different views therefore attempts to represent composite views as database tables is likely to rapidly multiply the size of the database. The example below is just one of many cases where creating concrete database tables to accommodate composite views might seem an attractive idea. However, pursuing this would create redundant data with few benefits, a major impact on storage requirements and a significantly more complex maintenance process when reviewing and installing future release packages. In contrast, representing composite views as database views, ensures the data is derived in real-time from tables representing the authoritative content of the full and/or snapshot release files.\\

{% hint style="info" %}
**Example**

Most English language descriptions are either preferred or acceptable in both US and GB english. Therefore instantiating tables that represent the sets of preferred and acceptable terms in either or both dialects would not only duplicate much of the data in that table but would require even more space to duplicate the relevant indexes. In addition to the impact of disk space, data duplicated in these composite tables would need updating to take account of new releases.
{% endhint %}

## Composite Description Views

Composite description views enable access to appropriate individual descriptions or sets of descriptions in a specific language or dialect. There are two sets of composite description views. Views in the first of these sets are designed to facilitate selecting descriptions associated with one or more identified concepts. Views in the other set are designed to enable searching descriptions to find concepts.

### Views that Facilitate Selecting Concept Descriptions <a href="#id-4.8.2.compositedescriptionviews-viewsthatfacilitateselectingconceptdescriptions" id="id-4.8.2.compositedescriptionviews-viewsthatfacilitateselectingconceptdescriptions"></a>

For each snapshot view the SNOMED CT example database includes four views designed for selecting specific sets of descriptions for one or more specified concepts. The characteristics of each of these views are shown in Table 4 and a general template for the SQL definitions of these views is shown in Template 1. To create each of the views named in the table, the placeholders for {typeId} and {acceptabilityId} need to be replaced with values in the *Specific Settings* column of the table.

Example 1 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

**Table 4: Composite Description Views for Selecting Concept Descriptions**

<table data-header-hidden><thead><tr><th></th><th width="227.42578125"></th><th width="210.703125"></th><th width="213.515625"></th></tr></thead><tbody><tr><td><a data-footnote-ref href="#user-content-fn-4"><strong>Name</strong></a></td><td><strong>Description</strong></td><td><strong>Specific Setting - {typeId}</strong></td><td><strong>Specific Setting - {acceptabilityId}</strong></td></tr><tr><td>snap_<strong>fsn</strong></td><td>This view selects the fully specified name of a concept (identified by conceptId)</td><td>= 900000000000003001</td><td>= 900000000000548007</td></tr><tr><td>snap_<strong>pref</strong></td><td>This view selects the preferred synonym of a concept (identified by conceptId)</td><td>= 900000000000013009</td><td>= 900000000000548007</td></tr><tr><td>snap_<strong>syn</strong></td><td>This view selects other acceptable synonyms of a concept (identified by conceptId)</td><td>= 900000000000013009</td><td>= 900000000000549004</td></tr><tr><td>snap_<strong>synall</strong></td><td>This view selects all synonyms of a concept (identified by conceptId)</td><td>= 900000000000013009</td><td><a data-footnote-ref href="#user-content-fn-5">IN (900000000000548007, 900000000000549004)</a></td></tr></tbody></table>

**Template 1: Description Selection Composite View Template**

```sql
CREATE VIEW `snap_{name}` AS
(SELECT `d`.* FROM `snap_description` `d`
    JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId`
    JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId`
    WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId}
    AND `rs`.`active` = 1 AND `rs`.`acceptabilityId` {acceptabilityId}
    AND `cfg`.`id`=0);
```

**Example 1: Selecting Description Data for a Concept**

**SQL Query**

```sql
Select conceptId,'FSN',id,term from snap_fsn where conceptId=95570007
UNION
Select conceptId,'Pref',id,term from snap_pref where conceptId=95570007
UNION
Select conceptId,'Syn',id,term from snap_syn where conceptId=95570007;
```

**Results**

| **conceptId** | **FSN** | **id**    | **term**                |
| ------------- | ------- | --------- | ----------------------- |
| 95570007      | FSN     | 839752010 | Kidney stone (disorder) |
| 95570007      | Pref    | 158296018 | Kidney stone            |
| 95570007      | Syn     | 158297010 | Renal stone             |
| 95570007      | Syn     | 158298017 | Nephrolith              |
| 95570007      | Syn     | 158299013 | Renal calculus          |
| 95570007      | Syn     | 512193015 | Calculus of kidney      |
| 95570007      | Syn     | 512194014 | Nephrolithiasis         |
| 95570007      | Syn     | 512195010 | Kidney calculus         |

### Views that Facilitate Searching for Concepts

For each snapshot view the SNOMED CT example database includes two views that are designed to facilitate searches for concepts associated with terms that match specified criteria. These views require both the description and the associated concept to be active in the chosen snapshot. [This avoids the need to filter out inactive concepts from the search results](#user-content-fn-6)[^6]. The specific characteristics of these views is shown in Table 5 and a general template for the SQL definitions of these views is shown in Template 2. To create the each of the views named in the table, the placeholder for {typeId} needs to be replaced with values in the *Specific Settings* column of the table.

Example 2 shows a query that searches the snap\_syn\_search\_active view using MySQL's boolean full text search. This search method requires all the words preceded by + (plus) to be included and excludes all words preceded by - (minus). The results are shown together with the fully specified name of the concept (looked up using the snap\_fsn view). Although this is not a user-friendly way to specify a searches, the example SQL code illustrates the technical power of this search technique. Other search techniques can also be applied to the search views and additional options for enhancing searches are discussed in[ Search Procedures](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.9-stored-procedures.md#search-procedures).

**Table 5: Composite Description Views that Facilitate Searching for Concepts**

| [**Name**](#user-content-fn-4)[^4] | **Description**                                                                                                                                                                                                            | **Specific Settings - {typeId}**                                      |
| ---------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------- |
| snap\_**syn\_search\_active**      | This view includes active preferred and acceptable synonyms of active concepts. It excludes fully specified names and also excludes all descriptions associated with concepts that are inactive in the specified snapshot. | = 900000000000013009                                                  |
| snap\_**term\_search\_activ**e     | This view includes active preferred and acceptable synonyms of active concepts.                                                                                                                                            | I[N (900000000000003001, 900000000000013009)](#user-content-fn-7)[^7] |

**Template 2: Description Search Composite View Template**

```sql
CREATE VIEW `snap_syn_search_active` AS
(SELECT `d`.*,`rs`.`acceptabilityId` FROM `snap_description` `d`
    JOIN `snap_refset_Language` `rs` ON `d`.`id` = `rs`.`referencedComponentId`
    JOIN `snap_concept` `c` ON `c`.`id` = `d`.`conceptId`
    JOIN `config_settings` `cfg` ON `rs`.`refSetId` = `cfg`.`languageId`
    WHERE `d`.`active` = 1 AND `d`.`typeId` {typeId}
    AND `rs`.`active` = 1
    AND `c`.`active` = 1
    AND `cfg`.`id`=0);
```

#### Usage Example

**Example 2: Searching Terms to Find Concepts**

**SQL Query**

```sql
SELECT `s`.`conceptId`,`s`.`term` 'matching term',`f`.`term` `FSN` FROM `snap_syn_search_active` `s`
    JOIN `snap_fsn` `f` ON `f`.`conceptId`=`s`.`conceptId`
    WHERE MATCH (`s`.`term`)
            AGAINST ('+acute +anterior +myocardial +infarction -ecg -old -ekg' IN BOOLEAN MODE) ORDER BY length(`f`.`term`),length(`s`.`term`);
```

**Result**

| **conceptId**   | **matching term**                                                                                    | **FSN**                                                                                                        |
| --------------- | ---------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- |
| 54329005        | Acute anterior myocardial infarction                                                                 | Acute myocardial infarction of anterior wall (disorder)                                                        |
| 54329005        | Acute myocardial infarction of anterior wall                                                         | Acute myocardial infarction of anterior wall (disorder)                                                        |
| 703164000       | Acute anterior ST segment elevation myocardial infarction                                            | Acute ST segment elevation myocardial infarction of anterior wall (disorder)                                   |
| 703164000       | Acute STEMI (ST elevation myocardial infarction) of anterior wall                                    | Acute ST segment elevation myocardial infarction of anterior wall (disorder)                                   |
| 703164000       | Acute ST segment elevation myocardial infarction of anterior wall                                    | Acute ST segment elevation myocardial infarction of anterior wall (disorder)                                   |
| 703252002       | Acute myocardial infarction of anterior wall involving right ventricle                               | Acute myocardial infarction of anterior wall involving right ventricle (disorder)                              |
| 703252002       | Acute myocardial infarction of anterior wall with right ventricular involvement                      | Acute myocardial infarction of anterior wall involving right ventricle (disorder)                              |
| 703165004       | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle          | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)         |
| 703165004       | Acute anterior ST segment elevation myocardial infarction with right ventricular involvement         | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)         |
| 703165004       | Acute STEMI (ST elevation myocardial infarction) of anterior wall with right ventricular involvement | Acute ST segment elevation myocardial infarction of anterior wall involving right ventricle (disorder)         |
| 285981000119103 | Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery  | Acute ST segment elevation myocardial infarction involving left anterior descending coronary artery (disorder) |

## Composite Subtype Hierarchy Views

Composite subtype hierarchy views enable selection of supertypes and subtypes of specified *concepts* accompanied by human-readable terms for each the selected concepts. The hierarchy views include supertype parents, subtype children, supertype ancestors and subtype descendants of specified *concepts*. More specialized views are also included to list the proximal primitive parents of a specified *concepts* and to list *concepts* that share a specified *proximal primitive parent*. [All these views use include a human-readable term (either the fully specified name or the preferred synonym) for each concept listed in the output.](#user-content-fn-8)[^8]

### Supertype Parent and Subtype Child Views

For each snapshot view the SNOMED CT example database includes two views that select the supertype parents of a specified concept and two views that select the subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in Table 6 and a general template for the SQL definitions of these views is shown in Template 3. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the *Specific Settings* column of the table.

Example 3 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype parents and subtype children.

**Table 6: Composite Views of Supertype Parents and Subtype Children**

<table data-header-hidden><thead><tr><th width="198.91015625">Name</th><th width="284.21484375">Description</th><th>Specific Settings - {termtype}</th></tr></thead><tbody><tr><td><a data-footnote-ref href="#user-content-fn-4"><strong>Name</strong></a></td><td><strong>Description</strong></td><td><strong>Specific Settings - {termtype}</strong></td></tr><tr><td>snap_rel_parent_fsn</td><td>Selects the id and fully specified name of each supertype parent of a concept specified by conceptId.</td><td>fsn</td></tr><tr><td>snap_rel_parent_pref</td><td>Selects the id and preferred synonym of each supertype parent of a concept specified by conceptId.</td><td>pref</td></tr><tr><td>snap_rel_child_fsn</td><td>Selects the id and fully specified name of each subtype child of a concept specified by conceptId.</td><td>fsn</td></tr><tr><td>snap_rel_child_pref</td><td>Selects the id and preferred synonym of each subtype child of a concept specified by conceptId.</td><td>pref</td></tr></tbody></table>

**Template 3: SQL Templates for Composite Views of Supertype Parents and Subtype Children**

```sql
-- Supertype Parent View
CREATE VIEW `snap_rel_parent_{termtype}` AS
    SELECT `r`.`targetId` AS `id`, `d`.`term` AS `term`, `r`.`sourceId` AS `conceptId`
    FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`targetId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;
-- Subtype Child View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_rel_child_{termtype}` AS
    SELECT `r`.`sourceId` AS `id`, `d`.`term` AS `term`, `r`.`destinationId` AS `conceptId`
    FROM `snap_relationship` `r` JOIN `snap_{termtype}` `d` ON `r`.`sourceId` = `d`.`conceptId`
    WHERE `r`.`active` = 1  AND `r`.`typeId` = 116680003;
```

**Example 3: Selecting Supertype Parents and Subtype Children**

**SQL Query**

```sql
Select "Concept", conceptid, term from snap_pref where conceptId=6025007
UNION
Select "Supertype Parent", id, term from snap_rel_parent_pref where conceptId=6025007
UNION
Select "Subtype Child", id, term from snap_rel_child_pref where conceptId=6025007;
```

**Result**

| **Concept**      | **id**    | **term**                                 |
| ---------------- | --------- | ---------------------------------------- |
| *Concept*        | *6025007* | *Laparoscopic appendectomy*              |
| Supertype Parent | 51316009  | Laparoscopic procedure                   |
| Supertype Parent | 80146002  | Appendectomy                             |
| Supertype Parent | 264274002 | Endoscopic operation                     |
| Supertype Parent | 440588003 | Endoscopic procedure on appendix         |
| Subtype Child    | 174041007 | Laparoscopic emergency appendectomy      |
| Subtype Child    | 307581005 | Laparoscopic interval appendectomy       |
| Subtype Child    | 708876004 | Robot assisted laparoscopic appendectomy |

### Transitive Closure Views of Supertype Ancestors and Subtype Descendants <a href="#id-4.8.3.compositesubtypehierarchyviews-transitiveclosureviewsofsupertypeancestorsandsubtypedescenda" id="id-4.8.3.compositesubtypehierarchyviews-transitiveclosureviewsofsupertypeancestorsandsubtypedescenda"></a>

For each snapshot view the SNOMED CT example database includes two views that select the supertype ancestors of a specified concept and two views that select the subtype descendants of a specified concept. These views select the id and either the fully specified name or preferred synonym of each ancestor or descendant concept. The characteristics of each of these views are shown in Table 7 and a general template for the SQL definitions of these views is shown in Template 4. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the *Specific Settings* column of the table.

Example 4 demonstrates the use of these views to select the id and preferred term for a specified concept and all of its supertype ancestors and subtype descendants.

**Table 7: Transitive Closure Views of Supertype Ancestors and Subtype Descendants**

<table data-header-hidden><thead><tr><th></th><th></th><th width="178.73828125"></th></tr></thead><tbody><tr><td><a data-footnote-ref href="#user-content-fn-9"><strong>Name</strong></a></td><td><strong>Description</strong></td><td><strong>Specific Settings -</strong><br><strong>{termtype}</strong></td></tr><tr><td><pre><code>snap_tc_ancestor_fsn
</code></pre></td><td>Selects the id and fully specified name of each supertype ancestor of a concept specified by conceptId.</td><td>fsn</td></tr><tr><td><pre><code>snap_tc_ancestor_pref
</code></pre></td><td>Selects the id and preferred synonym of each supertype ancestor of a concept specified by conceptId.</td><td>pref</td></tr><tr><td><pre><code>snap_tc_descendant_fsn
</code></pre></td><td>Selects the id and fully specified name of each subtype descendant of a concept specified by conceptId.</td><td>fsn</td></tr><tr><td><pre><code>snap_tc_descendant_pref
</code></pre></td><td>Selects the id and preferred synonym of each subtype descendant of a concept specified by conceptId.</td><td>pref</td></tr></tbody></table>

**Template 4: SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants**

```sql
-- Supertype Ancestor View: Differences are `sourceId` changed to `targetId` and `targetId` changed to `sourceId` as shown below
CREATE VIEW `snap_tc_ancestor_{termtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_transclose` `r`
    JOIN `snap_{termtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
 
 
--- Subtype Descendant View
CREATE VIEW `snap_tc_descendant_{termtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_transclose` `r`
    JOIN `snap_{termtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
```

**Example 4: Selecting Supertype Ancestors and Subtype Descendants**

**SQL Query**

```sql
Select "Concept", conceptid, term from snap_pref where conceptId=16001004
UNION
Select "Ancestor", id, term from snap_tc_ancestor_pref where conceptId=16001004
UNION
Select "Descendant", id, term from snap_tc_descendant_pref where conceptId=16001004;
```

**Result**

| **Concept** | **conceptId**    | **term**                           |
| ----------- | ---------------- | ---------------------------------- |
| *Concept*   | *16001004*       | *Otalgia*                          |
| Ancestor    | 22253000         | Pain                               |
| Ancestor    | 102957003        | Neurological finding               |
| Ancestor    | 106147001        | Sensory nervous system finding     |
| Ancestor    | 118234003        | Finding by site                    |
| Ancestor    | 118236001        | Ear and auditory finding           |
| Ancestor    | 118254002        | Finding of head and neck region    |
| Ancestor    | 138875005        | SNOMED CT Concept                  |
| Ancestor    | 247234006        | Ear finding                        |
| Ancestor    | 276435006        | Pain / sensation finding           |
| Ancestor    | 279001004        | Pain finding at anatomical site    |
| Ancestor    | 297268004        | Ear, nose and throat finding       |
| Ancestor    | 301354004        | Pain of ear structure              |
| Ancestor    | 301857004        | Finding of body region             |
| Ancestor    | 404684003        | Clinical finding                   |
| Ancestor    | 406122000        | Head finding                       |
| Ancestor    | 699697007        | Finding of sensation by site       |
| Descendant  | 12336008         | Referred otalgia                   |
| Descendant  | 74123003         | Otogenic otalgia                   |
| Descendant  | 162356005        | Earache symptoms                   |
| Descendant  | 162359003        | Bilateral earache                  |
| Descendant  | 430879002        | Posterior auricular pain           |
| Descendant  | 1084561000119106 | Bilateral referred otalgia of ears |
| Descendant  | 1089561000119107 | Referred otalgia of left ear       |
| Descendant  | 1092171000119100 | Referred otalgia of right ear      |

### Proximal Primitive Parent Views <a href="#id-4.8.3.compositesubtypehierarchyviews-proximalprimitiveparentviews" id="id-4.8.3.compositesubtypehierarchyviews-proximalprimitiveparentviews"></a>

For each snapshot view the SNOMED CT example database includes two views that select the proximal primitive parents of a specified concept and two views that select the subtype children of a specified concept. These views select the id and either the fully specified name or preferred synonym of each parent or child concept. The characteristics of each of these views are shown in Table 8 and a general template for the SQL definitions of these views is shown in Template 5. To create each of the views named in the table, the placeholders for {viewtype} need to be replaced with values in the *Specific Settings* column of the table.

Example 5 demonstrates the use of these views to select the id and preferred term for a specified concept and its *proximal primitive parents*. Example 6 demonstrates the use of these views to select the id and preferred term for a specified concept and all the concepts that have this concept as a *proximal primitive parent*.

**Table 8: Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor**

| [**Name**](#user-content-fn-10)[^10] | **Description**                                                                                                    | <p><strong>Specific Settings -</strong><br><strong>{viewtype}</strong></p> |
| ------------------------------------ | ------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------- |
| snap\_pp\_parent\_fsn                | Selects the id and fully specified name of each *proximal primitive parent* of a concept specified by conceptId.   | fsn                                                                        |
| snap\_pp\_parent\_pref               | Selects the id and preferred synonym of each *proximal primitive parent* of a concept specified by conceptId.      | pref                                                                       |
| snap\_pp\_child\_fsn                 | Selects the id and fully specified name of each concept with a *proximal primitive parent* specified by conceptId. | fsn                                                                        |
| snap\_pp\_child\_pref                | Selects the id and preferred synonym of each concept with a *proximal primitive parent* specified by conceptId.    | pref                                                                       |

**Template 5: SQL Templates for Proximal Primitive Supertype Views**

```sql
-- Proximal primitive parents of a specified concept
CREATE VIEW `snap_pp_parent_{viewtype}` AS
(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
 
 
-- Concepts with a specified proximal primitive parent concept
CREATE VIEW `snap_pp_child_{viewtype}` AS
(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
    FROM  `snap_proximal_primitives` `r`
    JOIN `snap_{viewtype}` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
```

**Example 5: Selecting Proximal Primitive Parents of a Concept**

**SQL Query**

```sql
--
Select "Concept", conceptid, term from snap_pref where conceptId=21522001
UNION
Select "Proximal Primitive Parent", id, term from snap_pp_parent_pref where conceptId=21522001;
```

**Result**

| **Concept**               | **conceptId** | **term**         |
| ------------------------- | ------------- | ---------------- |
| *Concept*                 | *21522001*    | *Abdominal pain* |
| Proximal Primitive Parent | 22253000      | Pain             |

**Example 6: Selecting Concepts with a Specified Proximal Primitive Parent**

**SQL Query**

```sql
Select "Concept", conceptid, term from snap_pref where conceptId=22253000
UNION
Select "Concept with PP-Parent: 21522001|Pain|", id, term from snap_pp_child_pref where conceptId=22253000;
```

**Result**

| **Concept**                               | **conceptId** | **term**           |
| ----------------------------------------- | ------------- | ------------------ |
| *Concept*                                 | *22253000*    | *Pain*             |
| Concept with PP-Parent: 21522001 \|Pain\| | 4448006       | Allergic headache  |
| Concept with PP-Parent: 21522001 \|Pain\| | 4568003       | Retrosternal pain  |
| Concept with PP-Parent: 21522001 \|Pain\| | 6561007       | Pain in urethra    |
| Concept with PP-Parent: 21522001 \|Pain\| | 10601006      | Pain in lower limb |
| Concept with PP-Parent: 21522001 \|Pain\| | 12584003      | Bone pain          |
| Concept with PP-Parent: 21522001 \|Pain\| | 15803009      | Bladder pain       |
| Concept with PP-Parent: 21522001 \|Pain\| | 16513000      | Postcordotomy pain |
| Concept with PP-Parent: 21522001 \|Pain\| | 18876004      | Pain in finger     |
| Concept with PP-Parent: 21522001 \|Pain\| | 20793008      | Scapulalgia        |
| Concept with PP-Parent: 21522001 \|Pain\| | 21522001      | Abdominal pain     |
| Concept with PP-Parent: 21522001 \|Pain\| | 21545007      | Tenalgia           |
| Concept with PP-Parent: 21522001 \|Pain\| | 29857009      | Chest pain         |
| Concept with PP-Parent: 21522001 \|Pain\| | 30473006      | Pain in pelvis     |
| Concept with PP-Parent: 21522001 \|Pain\| | 30989003      | Knee pain          |

| *... total of 240 rows returned ...* |
| ------------------------------------ |

## Composite Relationship Views

[The composite relationship views display active, inferred defining relationships of specified *concepts* accompanied by human-readable terms for each the selected concepts.](#user-content-fn-11)[^11]

### Defining Relationship View

For each snapshot view the SNOMED CT example database there are two view of the active, inferred defining relationships. The views select the id and either the fully specified name or preferred synonym for the concept identified in each of the defining columns (sourceId, typeId and destinationId). The characteristics of these views are shown in Table 9 and a general template for the SQL definitions of these views is shown in Template 6. To create each of the views named in the table, the placeholders for {termtype} need to be replaced with values in the *Specific Settings* column of the table.

Example 7 demonstrates the use of these views to select the active defining relationships of a specified concept with the id and preferred term for each of the referenced concepts.

**Table 9: Composite Views of Supertype Parents and Subtype Children**

<table data-header-hidden><thead><tr><th></th><th width="421.80078125"></th><th></th></tr></thead><tbody><tr><td><a data-footnote-ref href="#user-content-fn-4"><strong>Name</strong></a></td><td><strong>Description</strong></td><td><strong>Specific Settings -</strong><br><strong>{termtype}</strong></td></tr><tr><td>snap_rel_def_fsn</td><td><a data-footnote-ref href="#user-content-fn-12">This view includes all active, inferred relationships of a concept specified by sourceId</a>. It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number.</td><td>fsn</td></tr><tr><td>snap_rel_def_pref</td><td><a data-footnote-ref href="#user-content-fn-12">This view includes all active, inferred relationships of a concept specified by sourceId</a>. It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number.</td><td>pref</td></tr></tbody></table>

**Template 6: SQL Templates for Composite Views of Defining Relationships**

```sql
CREATE VIEW `snap_rel_def_{viewtype}` AS
(SELECT `r`.`sourceId` `sourceId`,`src`.`Term` `sourceTerm`,`r`.`typeId` `typeId`,`typ`.`Term` `typeTerm`,`r`.`destinationId` `destinationId`,`dest`.`Term` `destinationTerm`,`r`.`relationshipGroup` `relationshipGroup`
    FROM (((`snap_relationship` `r`
    JOIN `snap_{viewtype}` `src` ON ((`r`.`sourceId` = `src`.`conceptId`))) JOIN `snap_{viewtype}` `typ` ON ((`r`.`typeId` = `typ`.`conceptId`))) JOIN `snap_{viewtype}` `dest` ON ((`r`.`destinationId` = `dest`.`conceptId`))) WHERE ((`r`.`active` = 1) AND (`r`.`characteristicTypeId` = 900000000000011006)));
```

**Example 7: Selecting Supertype Parents and Subtype Children**

**SQL Query**

```sql
Select * from snap_rel_def_pref where sourceId=6025007;
```

**Result**

<table data-header-hidden><thead><tr><th></th><th width="143.04296875"></th><th></th><th></th><th></th><th width="150.53125"></th><th width="170.0546875"></th></tr></thead><tbody><tr><td><strong>sourceId</strong></td><td><strong>sourceTerm</strong></td><td><strong>typeId</strong></td><td><strong>typeTerm</strong></td><td><strong>destinationId</strong></td><td><strong>destinationTerm</strong></td><td><strong>relationshipGroup</strong></td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>116680003</td><td>Is a</td><td>51316009</td><td>Laparoscopic procedure</td><td>0</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>116680003</td><td>Is a</td><td>80146002</td><td>Appendectomy</td><td>0</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>116680003</td><td>Is a</td><td>264274002</td><td>Endoscopic operation</td><td>0</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>116680003</td><td>Is a</td><td>440588003</td><td>Endoscopic procedure on appendix</td><td>0</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>260686004</td><td>Method</td><td>129304002</td><td>Excision - action</td><td>1</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>405813007</td><td>Procedure site - Direct</td><td>66754008</td><td>Appendix structure</td><td>1</td></tr><tr><td>6025007</td><td>Laparoscopic appendectomy</td><td>425391005</td><td>Using access device</td><td>86174004</td><td>Laparoscope</td><td>1</td></tr></tbody></table>

## Composite Historical Views

### Inactive Concept Views <a href="#id-4.8.5.compositehistoricalviews-inactiveconceptviews" id="id-4.8.5.compositehistoricalviews-inactiveconceptviews"></a>

For each delta and snapshot view the SNOMED CT example database includes a view of inactive concepts. The characteristics of each of these views are shown in Table 10 and a general template for the SQL definitions of these views is shown in Template 7.

Example 8 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

**Table 10: Composite Views of Inactive Concepts with Related Concept Inactivation and Historical Association Refset Data**

<table data-header-hidden><thead><tr><th width="211.87109375"></th><th></th></tr></thead><tbody><tr><td><strong>Name</strong></td><td><strong>Description</strong></td></tr><tr><td>delta_inactive_concepts</td><td>This view selects details of concepts that are inactive in the chosen delta or snapshot view. In addition to the concept id the fully specified name of the inactive concept is selected. The output of this view also includes the reason for activation and any historical associations between this inactive concept and an active concept. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the concept inactivation reference set. The historical association is represented by the preferred synonym of the association reference set(s) and the fully specified name of the associated target concept. Where a concept has multiple active associations each of these reported as a separate row (the inactive concept and inactivation reason data is duplicated on each of these rows).</td></tr></tbody></table>

**Template 7: SQL Definition of the Inactive Concepts View**

```sql
CREATE VIEW delta_inactive_concepts AS
select `c`.`id`, `c`.`effectiveTime`, `c`.`active`, `c`.`definitionStatusId`, `cf`.`term` 'FSN',
    `vp`.`term` 'reason', `arp`.`term` 'assoc_type', `atf`.`id` 'ref_conceptId', `atf`.`term` 'ref_concept_FSN'
from `delta_concept` `c`
left join `snap_fsn` `cf` ON `cf`.`conceptid`=`c`.`id`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`c`.`id`
     and `v`.`refsetId`=900000000000489007 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid`
left outer join `snap_refset_association` `a` on `a`.`referencedComponentId`=`c`.`id` and `a`.`refsetId` IN
    (900000000000528000, 900000000000523009, 900000000000527005, 900000000000526001,
     900000000000525002, 900000000000531004, 900000000000524003, 900000000000530003) and `a`.`active`=1
left outer join `snap_pref` `arp` on `arp`.`conceptid`=`a`.`refsetId`
left outer join `snap_fsn` `atf` on `atf`.`conceptid`=`a`.`targetComponentId`
where `c`.`active`=0
order by `c`.`id`;
```

**Example 8: Selecting Inactive Concepts with Related Concepts Inactivation and Historical Association Refset Data**

**SQL Query**

```sql
SELECT * FROM delta_inactive_concepts;
```

**Result (example rows only)**

<table><thead><tr><th>id</th><th width="147.21875">effectiveTime</th><th width="92.265625">active</th><th width="215.0703125">definitionStatusId</th><th width="199.42578125">FSN</th><th>reason</th><th width="152.12890625">assoc_type</th><th width="156.57421875">ref_conceptId</th><th width="249.8984375">ref_concept_FSN</th></tr></thead><tbody><tr><td>1192004</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Familial amyloid neuropathy, Finnish type (disorder)</td><td>Outdated</td><td>REPLACED BY</td><td>3757892013</td><td>Hereditary gelsolin amyloidosis (disorder)</td></tr><tr><td>1230003</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>No diagnosis on Axis I (finding)</td><td>Outdated</td><td>REPLACED BY</td><td>677781011</td><td>Psychological finding (finding)</td></tr><tr><td>1427008</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Intraspinal abscess (disorder)</td><td>Duplicate</td><td>SAME AS</td><td>743297013</td><td>Spinal cord abscess (disorder)</td></tr><tr><td>2461007</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Tennis elbow test (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>3777085015</td><td>Lateral epicondylitis test (procedure)</td></tr><tr><td>2900003</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Hyperplasia of renal artery (disorder)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>3760067011</td><td>Fibromuscular dysplasia of wall of renal artery (disorder)</td></tr><tr><td>3105002</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Intron (finding)</td><td>Outdated</td><td>REPLACED BY</td><td>697643016</td><td>Finding related to molecular sequence data (finding)</td></tr><tr><td>3221003</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Ringer's solution (product)</td><td>Nonconformance to editorial policy component</td><td><br></td><td><br></td><td><br></td></tr><tr><td>3734003</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Split thickness skin graft (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>3758568011</td><td>Split thickness graft of skin to skin (procedure)</td></tr><tr><td>4101004</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Revision of spinal pleurothecal shunt (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>618942015</td><td>Revision of spinal subarachnoid shunt (procedure)</td></tr><tr><td>4101004</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Revision of spinal pleurothecal shunt (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>618681017</td><td>Revision of subdural-pleural shunt (procedure)</td></tr><tr><td>4131005</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Implantation into pelvic region (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>2968044014</td><td>Procedure on pelvic region of trunk (procedure)</td></tr><tr><td>4131005</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Implantation into pelvic region (procedure)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>3756616019</td><td>Implantation procedure (procedure)</td></tr><tr><td>4518006</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Buthenal (substance)</td><td>Ambiguous</td><td>POSSIBLY EQUIVALENT TO</td><td>796984014</td><td>Crotonaldehyde (substance)</td></tr><tr><td>4919007</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Congenital protrusion (morphologic abnormality)</td><td>Duplicate</td><td>SAME AS</td><td>642112018</td><td>Protrusion (morphologic abnormality)</td></tr><tr><td>5034009</td><td>20190731</td><td>0</td><td>900000000000074008</td><td>Graft to hair-bearing skin (procedure)</td><td>Duplicate</td><td>SAME AS</td><td>3757739014</td><td>Hair bearing graft of skin to skin (procedure)</td></tr></tbody></table>

### Inactive Descriptions <a href="#id-4.8.5.compositehistoricalviews-inactivedescriptions" id="id-4.8.5.compositehistoricalviews-inactivedescriptions"></a>

For each delta and snapshot view the SNOMED CT example database includes a view of inactive descriptions. The characteristics of each of these views are shown in Table 11 and a general template for the SQL definitions of these views is shown in Template 8.

Example 9 demonstrates the use of these views to show all the active descriptions of a specified concept that are acceptable or preferred according to the language reference set referenced by the configuration file.

**Table 11: Composite Views of Inactive Descriptions with Related Description Inactivation and Historical Association Refset Data**

<table data-header-hidden><thead><tr><th width="236.77734375"></th><th></th></tr></thead><tbody><tr><td><strong>Name</strong></td><td><strong>Description</strong></td></tr><tr><td>delta_inactive_descriptions</td><td>This view selects details of all descriptions that are inactive in the chosen delta or snapshot view. In addition to selecting the description data it also includes the active fully specified name of the related concept and the reason for activation. The reason for inactivation is shown as the preferred synonym for the concept representing the reason for inactivation in the description inactivation reference set.</td></tr></tbody></table>

**Template 8: SQL Definition of the Inactive Descriptions View**

```sql
CREATE VIEW delta_inactive_descriptions AS
select `d`.`id`, `d`.`effectiveTime`, `d`.`active`, `d`.`conceptid`, `d`.`term` 'term',
    `df`.`term` 'concept_fsn', `c`.`active` 'concept_active' ,`vp`.`term` 'reason'
from `delta_description` `d`
left outer join `snap_fsn` `df` ON `df`.`conceptid`=`d`.`conceptid`
join `snap_concept` `c` ON `c`.`id`=`d`.`conceptid`
left outer join `snap_refset_attributevalue` `v` on `v`.`referencedComponentId`=`d`.`id`
    and `v`.`refsetId`=900000000000490003 and `v`.`active`=1
left outer join `snap_pref` `vp` on `vp`.`conceptid`=`v`.`valueid`
where `d`.`active`=0
order by `d`.`id`;
```

**Example 9: Selecting Inactive Descriptions with Related Description Inactivation Refset Data**

**SQL Query**

```sql
SELECT * FROM delta_inactive_descriptions;
```

**Result**

<table data-header-hidden><thead><tr><th width="110.2109375"></th><th></th><th width="87.06640625"></th><th></th><th width="170.9765625"></th><th></th><th width="157.70703125"></th><th width="204.80078125"></th></tr></thead><tbody><tr><td><strong>id</strong></td><td><strong>effectiveTime</strong></td><td><strong>active</strong></td><td><strong>conceptid</strong></td><td><strong>term</strong></td><td><strong>concept_fsn</strong></td><td><strong>concept_active</strong></td><td><strong>reason</strong></td></tr><tr><td>14132019</td><td>20190731</td><td>0</td><td>7938006</td><td>D-Arabinitol dehydrogenase</td><td>D-arabinitol 4-dehydrogenase (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>16101018</td><td>20190731</td><td>0</td><td>9156001</td><td>Embryo stage 1</td><td>Structure of embryo at stage 1 (body structure)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>16837014</td><td>20190731</td><td>0</td><td>9631008</td><td>Rheumatoid spondylitis</td><td>Ankylosing spondylitis (disorder)</td><td>1</td><td>Not semantically equivalent component</td></tr><tr><td>17234017</td><td>20190731</td><td>0</td><td>9871000</td><td>D-Amino-acid acetyltransferase</td><td>D-amino-acid N-acetyltransferase (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>17525014</td><td>20190731</td><td>0</td><td>10043003</td><td>D-Alanine-alanyl-poly(glycerolphosphate) ligase</td><td>D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>17526010</td><td>20190731</td><td>0</td><td>10043003</td><td>D-Alanyl-alanyl-poly(glycerolphosphate)synthetase</td><td>D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>17527018</td><td>20190731</td><td>0</td><td>10043003</td><td>D-Alanine:membrane-acceptor ligase</td><td>D-alanine-alanyl-poly(glycerolphosphate) ligase (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>17615010</td><td>20190731</td><td>0</td><td>10093004</td><td>Anisakiasis due to Anisakis simplex</td><td>Anisakiasis caused by larva of Anisakis simplex (disorder)</td><td>1</td><td>Erroneous</td></tr><tr><td>20220015</td><td>20190731</td><td>0</td><td>11702002</td><td>bis-(p-Chlorophenyl) ethanol</td><td>Bis-(p-chlorophenyl) ethanol (substance)</td><td>1</td><td>Nonconformance to editorial policy component</td></tr><tr><td>20469015</td><td>20190731</td><td>0</td><td>11860003</td><td>Nannizzia</td><td>Genus Arthroderma (organism)</td><td>1</td><td>Not semantically equivalent component</td></tr></tbody></table>

<a href="https://docs.google.com/forms/d/e/1FAIpQLScTmbZIf0UEQwYDkY27EEWBkaiYkHSbR0_9DmFrMLXoQLyL7Q/viewform?usp=pp_url&#x26;entry.1767247133=SQL+Guide&#x26;entry.670899847=Composite%20Views" class="button primary">Provide Feedback</a>

[^1]: As noted in [Optimizing Versioned Table Views](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.6-enabling-versioned-views.md#title-text-1) the current snapshot may be represented as tables or database views. While this may make a difference to performance it does not make any difference to the design of composite views.

[^2]: In the SNOMED CT example database, most composite views have been created for the current snapshot (snap) and for both of the configurable retrospective snapshot views (snap1 and snap2). However, composite views that access either the transitive closure (snap\_transclose) or proximal primitives (snap\_proxprim) are not supported for the retrospective snapshots. This is because those tables are at present on available for the current snapshot view.

[^3]: The views snap\_fsn, snap1\_fsn and snap2\_fsn are composite views similar to snap\_pref but return the fully specified name rather than the preferred term.

[^4]: The prefix snap is replaced by snap1 or snap2 for retrospective views.

[^5]: An alternative way to represent snap\_synall is to remove the acceptability condition. The link to the language refset and the test for the 'rs'.'active' condition must retained to ensure only descriptions in the relevant language refset are returned.

[^6]: Requirements for searches that need to include inactive concepts can be run against the concept description selection views.

[^7]: Alternatively remove the typeId condition to permit all types to be searched.

[^8]: The terms are displayed by using the [Composite Description Views](#composite-description-views) described in the previous section.

[^9]: Transitive closure and proximal primitive views are only available for the current snapshot.

[^10]: Transitive closure and proximal primitive views are only available for the current snapshot

[^11]: The terms are displayed by using the [Composite Description Views](#composite-description-views) described in an earlier section of this guide.

[^12]: The selection criteria for any of these relationship views can also be specified by destinationId, typeId or by a combination of these identifiers. However, to see all the defining relationships of a specified concept, the sourceId should be used as this refers to the concept defined by the relationships.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snomed.org/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.8-composite-views.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.