...
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 and in
| Caption reference |
|---|
| CapRefId | rel-def-view |
|---|
| CapRefType | Table |
|---|
|
and a general template for the SQL definitions of these views is shown in
| Caption reference |
|---|
| CapRefId | rel-def-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 | rel-def-view |
|---|
| CapRefType | Example |
|---|
|
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.
| Caption label |
|---|
| CapId | rel-def-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_def_fsn | This view includes all active, inferred relationships of a concept specified by sourceId | Footnote Macro |
|---|
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. |
. 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 sourceId | Footnote Macro |
|---|
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. |
. It selects the id and fully specified name for each of the concept identifiers (sourceId, typeId and destinationId) and the relationshipGroup number. | |
| Caption label |
|---|
| CapId | rel-def-view |
|---|
| CapType | Template |
|---|
|
SQL Templates for Composite Views of Defining Relationships |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
|
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))); |
| Caption label |
|---|
| CapId | rel-def-view |
|---|
| CapType | Example |
|---|
|
Selecting Supertype Parents and Subtype Children |
| SQL Query |
| Code Block |
|---|
| language | sql |
|---|
| theme | Confluence |
|---|
| 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 |
...