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
,
,
and
of specified
concepts. More specialized views are also included to list the
Gloss |
---|
t | 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
of a specified concept and two views that select the
of a specified concept. These views select the id and either the fully specified name or preferred synonym each parent or child concept. The characteristics of each of these views are shown in
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Table |
---|
|
and a general template for the SQL definitions of these views is shown in
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Template |
---|
|
. 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.
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Example |
---|
|
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.
Caption label |
---|
CapId | parent-child-view |
---|
CapType | Table |
---|
|
Composite Views of Supertype Parents and Subtype Children |
Name Footnote Macro |
---|
The prefix snap is replaced by snap1 or snap2 for retrospective views. |
| Description | Specific Settings |
{termtype} |
snap_rel_parent_fsn | Selects the id and fully specified nameof each supertype parent of a concept specified by conceptId. | fsn |
snap_rel_parent_pref | Selects the id and preferred synonymof each supertype parent of a concept specified by conceptId. | pref |
snap_rel_child_fsn | Selects the id and fully specified name of each subtype child of a concept specified by conceptId. | fsn |
snap_rel_child_pref | Selects the id and preferred synonym of each subtype child of a concept specified by conceptId. | |
Caption label |
---|
CapId | parent-child-view |
---|
CapType | Template |
---|
|
SQL Templates for Composite Views of Supertype Parents and Subtype Children |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
|
-- 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; |
Caption label |
---|
CapId | parent-child-view |
---|
CapType | Example |
---|
|
Selecting Supertype Parents and Subtype Children |
SQL Query |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
| 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
For each snapshot view the SNOMED CT example database includes two views that select the
of a specified concept and two views that select the
of a specified concept. These views select the id and either the fully specified name or preferred synonym each ancestor or descendant concept. The characteristics of each of these views are shown in
Caption reference |
---|
CapRefId | transitive-closure-view |
---|
CapRefType | Table |
---|
|
and a general template for the SQL definitions of these views is shown in
Caption reference |
---|
CapRefId | transitive-closure-view |
---|
CapRefType | Template |
---|
|
. 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.
Caption reference |
---|
CapRefId | transitive-closure-view |
---|
CapRefType | Example |
---|
|
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.
Caption label |
---|
CapId | transitive-closure-view |
---|
CapType | Table |
---|
|
Transitive Closure Views of Supertype Ancestors and Subtype Descendants |
Name Footnote Macro |
---|
Transitive closure and proximal primitive views are only available for the current snapshot. |
.
| Description | Specific Settings |
{myph} |
snap_tc_ancestor_fsn | Selects the id and fully specified name of each supertype ancestor of a concept specified by conceptId. | fsn |
snap_tc_ancestor_pref | Selects the id and preferred synonym of each supertype ancestor of a concept specified by conceptId. | |
snap_tc_descendant_fsn | Selects the id and fully specified name of each subtype descendant of a concept specified by conceptId. | fsn |
snap_tc_descendant_pref | Selects the id and preferred synonym of each subtype descendant of a concept specified by conceptId. | |
Caption label |
---|
CapId | transitive-closure-view |
---|
CapType | Template |
---|
|
SQL Templates for Composite Views of Supertype Ancestors and Subtype Descendants |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
|
-- 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`)); |
Caption label |
---|
CapId | transitive-closure-view |
---|
CapType | Example |
---|
|
Selecting Supertype Ancestors and Subtype Descendants |
SQL Query |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
| Select "Concept", conceptid, term from snap_pref where conceptId=6025007
UNION
Select "Ancestor", id, term from snap_tc_ancestor_pref where conceptId=6025007
UNION
Select "Descendant", id, term from snap_tc_descendant_pref where conceptId=6025007; |
|
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
For each snapshot view the SNOMED CT example database includes two views that select the
Gloss |
---|
t | proximal primitive parents |
---|
|
of a specified concept and two views that select the
of a specified concept. These views select the id and either the fully specified name or preferred synonym each parent or child concept. The characteristics of each of these views are shown in
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Table |
---|
|
and a general template for the SQL definitions of these views is shown in
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Template |
---|
|
. 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.
Caption reference |
---|
CapRefId | parent-child-view |
---|
CapRefType | Example |
---|
|
and
Caption reference |
---|
CapRefId | parent-child-view-2 |
---|
CapRefType | Example |
---|
|
demonstrate 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.
For each snapshot view the SNOMED CT example database includes ... . The characteristics of each of these views are shown in
Caption reference |
---|
CapRefId | proximal-primitive-view |
---|
CapRefType | Table |
---|
|
and a general template for the SQL definitions of these views is shown in
Caption reference |
---|
CapRefId | proximal-primitive-view |
---|
CapRefType | Template |
---|
|
. To create each of the views named in the table, the placeholders for
{myph
} need to be replaced with values in the
Specific Settings column of the table.
Caption reference |
---|
CapRefId | proximal-primitive-view |
---|
CapRefType | Example |
---|
|
demonstrates the use of these views to select the id and preferred term for a specified concept and its
proximal primitive parents.
Caption reference |
---|
CapRefId | proximal-primitive-view-2 |
---|
CapRefType | Example |
---|
|
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.
Caption label |
---|
CapId | proximal-primitive-view |
---|
CapType | Table |
---|
|
Views of Proximal Primitive Supertype Ancestors and Concepts with a Specific Proximal Primitive Ancestor |
Name Footnote Macro |
---|
Transitive closure and proximal primitive views are only available for the current snapshot. |
| Description | Specific Settings |
{viewtype} |
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. | |
snap_pp_child_pref | Selects the id and preferred synonymof each concept with a proximal primitive parent specified by conceptId. | |
Caption label |
---|
CapId | proximal-primitive-view |
---|
CapType | Template |
---|
|
SQL Templates for Proximal Primitive Supertype Views |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
|
-- 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`)); |
Caption label |
---|
CapId | proximal-primitive-view |
---|
CapType | Example |
---|
|
Selecting Proximal Primitive Parents of a Concept |
SQL Query |
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
| --
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 |
Caption label |
---|
CapId | proximal-primitive-view-2 |
---|
CapType | Example |
---|
|
Selecting Concepts with a Specified Proximal Primitive Parent |
|
Code Block |
---|
language | sql |
---|
theme | Confluence |
---|
| 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 ... |
.