The composite relationship views display active, inferred defining relationships of specified concepts accompanied by human-readable terms for each the selected concepts 1 .
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 4.8.4-1 and a general template for the SQL definitions of these views is shown in Template 4.8.4-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.4-1 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 4.8.4-1: Composite Views of Supertype Parents and Subtype Children
Name2 | Description | Specific Settings |
| {termtype} | ||
snap_rel_def_fsn | This view includes all active, inferred relationships of a concept specified by sourceId3 . It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number. | fsn |
snap_rel_def_pref | This view includes all active, inferred relationships of a concept specified by sourceId3 . It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number. | pref |
Template 4.8.4-1: SQL Templates for Composite Views of Defining Relationships
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 4.8.4-1: Selecting Supertype Parents and Subtype Children
| SQL Query | ||||||
Select * from snap_rel_def_pref where sourceId=6025007; | ||||||
| Result | ||||||
| sourceId | sourceTerm | typeId | typeTerm | destinationId | destinationTerm | relationshipGroup |
| 6025007 | Laparoscopic appendectomy | 116680003 | Is a | 51316009 | Laparoscopic procedure | 0 |
| 6025007 | Laparoscopic appendectomy | 116680003 | Is a | 80146002 | Appendectomy | 0 |
| 6025007 | Laparoscopic appendectomy | 116680003 | Is a | 264274002 | Endoscopic operation | 0 |
| 6025007 | Laparoscopic appendectomy | 116680003 | Is a | 440588003 | Endoscopic procedure on appendix | 0 |
| 6025007 | Laparoscopic appendectomy | 260686004 | Method | 129304002 | Excision - action | 1 |
| 6025007 | Laparoscopic appendectomy | 405813007 | Procedure site - Direct | 66754008 | Appendix structure | 1 |
| 6025007 | Laparoscopic appendectomy | 425391005 | Using access device | 86174004 | Laparoscope | 1 |
| Ref | Notes |
|---|---|
| 1 | The terms are displayed by using the Composite Description Views described in an earlier section of this guide. |
| 2 | The prefix snap is replaced by snap1 or snap2 for retrospective views. |
| 3 | 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. [ a b ] |
Feedback
Overview
Content Tools
Apps