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

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 4.8.3-1 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-1. 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.8.3-1 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 4.8.3-1: Composite Views of Supertype Parents and Subtype Children

Template 4.8.3-1: SQL Templates for Composite Views of Supertype Parents and Subtype Children

-- 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 4.8.3-1: Selecting Supertype Parents and Subtype Children

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;

Transitive Closure Views of Supertype Ancestors and Subtype Descendants

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 4.8.3-2 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-2. 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.8.3-2 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 4.8.3-2: Transitive Closure Views of Supertype Ancestors and Subtype Descendants

snap_tc_ancestor_fsn

snap_tc_ancestor_pref

snap_tc_descendant_fsn

snap_tc_descendant_pref

Template 4.8.3-2: SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants

-- 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.8.3-2: Selecting Supertype Ancestors and Subtype Descendants

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;

Proximal Primitive Parent Views

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 4.8.3-3 and a general template for the SQL definitions of these views is shown in  Template 4.8.3-3. 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 4.8.3-3 demonstrates the use of these views to select the id and preferred term for a specified concept and its proximal primitive parents.  Example 4.8.3-4 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 4.8.3-3: Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor

Template 4.8.3-3: SQL Templates for Proximal Primitive Supertype Views

-- 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 4.8.3-3: Selecting Proximal Primitive Parents of a Concept

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

Example 4.8.3-4: Selecting Concepts with a Specified Proximal Primitive Parent

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;

.