Search



Page tree

  

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

DescriptionSpecific 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
sourceIdsourceTermtypeIdtypeTermdestinationIddestinationTermrelationshipGroup
6025007Laparoscopic appendectomy116680003Is a51316009Laparoscopic procedure0
6025007Laparoscopic appendectomy116680003Is a80146002Appendectomy0
6025007Laparoscopic appendectomy116680003Is a264274002Endoscopic operation0
6025007Laparoscopic appendectomy116680003Is a440588003Endoscopic procedure on appendix0
6025007Laparoscopic appendectomy260686004Method129304002Excision - action1
6025007Laparoscopic appendectomy405813007Procedure site - Direct66754008Appendix structure1
6025007Laparoscopic appendectomy425391005Using access device86174004Laparoscope1



Footnotes
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
  • No labels