UMLS Database Query Diagrams:
How to perform searches for root and versioned source metadata concepts

This diagram shows how to perform searches for root and versioned source metadata concepts. Each source contained within the UMLS is also represented as a concept in the UMLS. In fact, it is represented as two concepts: one with version information and one without. These are all labeled with SAB = 'SRC' and thus are known as "SRC" concepts. The "root SRC" concept (unversioned) remains the same as versions of that source are updated. The "versioned SRC" concept changes as updates of that source are applied to the UMLS. These concepts can always be found by their code values.

In addition to tracking the various names of the source, these concepts serve another important function. The tree-top atom of most source hierarchies is in the corresponding "root SRC" concept. The tree-top atom of a source's hierarchy is typically a SAB = 'SRC' and TTY = 'RHT' atom in the root SRC concept (unless the source names its own context root).

Corresponding Oracle Queries:

1.a. Find atoms of MSH root SRC concept.

SELECT * FROM mrconso
WHERE code = 'V-MSH'
     AND sab = 'SRC';

1.b. Find atoms of MSH versioned SRC concept.

SELECT * FROM mrconso
WHERE code = 'V-MSH2007_2007_05_01'
     AND sab = 'SRC';

2.a. Find semantic type of MSH root SRC concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the RPT atom that may be useful.

SELECT * FROM mrconso a, mrsty b
WHERE a.code = 'V-MSH'
     AND a.tty = 'RPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui;

2.b. Find semantic type of MSH versioned SRC concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the VPT atom that may be useful.

SELECT b.* FROM mrconso a, mrsty b
WHERE a.code = 'V-MSH2007_2007_05_01'
     AND a.tty = 'VPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui;

3.a. Find attributes of MSH root SRC concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the RPT atom that may be useful.

SELECT b.* FROM mrconso a, mrsat b
WHERE a.code = 'V-MSH'
     AND a.tty = 'RPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui;

3.b. Find attributes of MSH versioned SRC concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the VPT atom that may be useful.

SELECT b.* FROM mrconso a, mrsat b
WHERE a.code = 'V-MSH2007_2007_05_01'
     AND a.tty = 'VPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui;

4.a. Find the MSH VPT atom through MRREL.RRF from the MSH RPT atom.

SELECT c.* FROM mrconso a, mrrel b, mrconso c
WHERE a.code = 'V-MSH'
     AND a.tty = 'RPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui1
     AND b.rela = 'version_of'
     AND b.cui2 = c.cui
     AND c.tty = 'VPT';

4.b. Find the MSH RPT atom through MRREL.RRF from the MSH VPT atom.

SELECT c.* FROM mrconso a, mrrel b, mrconso c
WHERE a.code = 'V-MSH2007_2007_05_01'
     AND a.tty = 'VPT'
     AND a.sab = 'SRC'
     AND a.cui = b.cui1
     AND b.rela = 'has_version'
     AND b.cui2 = c.cui
     AND c.tty = 'RPT';

Last Reviewed: July 29, 2016