UMLS Database Query Diagrams:
How to find all information associated with the metadata of a particular atom (AUI value)

This diagram shows how to find all information associated with the metadata of a particular atom (AUI value). Joining SAB, TTY on MRRANK.RRF will yield rank and suppressibility information. Searching for a LAT, TS, STT, TTY, or SUPPRESS value in MRDOC.RRF (where DOCKEY is the field name) will yield expanded forms of the abbreviations used within MRCONSO.RRF.

When searching for TTY values in MRDOC.RRF there are two different types of information found. One ("expanded_form") will yield an expanded form of the abbreviated TTY value used within MRCONSO.RRF. The other ("tty_class") will provide information about high-level groupings of TTY values. As of the UMLS 2008AB Release there are nine TTY groupings used: abbreviation, attribute, entry_term, expanded, hierarchical, obsolete, other, preferred, or synonym.

Finally, searching for SAB values in MRSAB.RRF will yield detailed source provider information.

Corresponding Oracle Queries:

1. Find an atom.

SELECT * FROM mrconso
WHERE aui = 'A10442923';

2. Find SAB, TTY information for an atom.

SELECT a.* FROM mrrank a, mrconso b
WHERE aui = 'A10442923'
     AND a.sab = b.sab
     AND a.tty = b.tty;

3. Find expanded form of a LAT for an atom.

SELECT a.expl FROM mrdoc a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.value = b.lat
     AND a.type = 'expanded_form'
     AND a.dockey = 'LAT';

4. Find expanded form of a TS for an atom.

SELECT a.expl FROM mrdoc a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.value = b.ts
     AND a.type = 'expanded_form'
     AND a.dockey = 'TS';

5. Find expanded form of a STT for an atom.

SELECT a.expl FROM mrdoc a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.value = b.stt
     AND a.type = 'expanded_form'
     AND a.dockey = 'STT';

6. Find expanded form of a TTY for an atom.

SELECT a.expl FROM mrdoc a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.value = b.tty
     AND a.type = 'expanded_form'
     AND a.dockey = 'TTY';

7. Find expanded form of the SUPPRESS value for an atom.

SELECT a.expl FROM mrdoc a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.value = b.suppress
     AND a.type = 'expanded_form'
     AND a.dockey = 'SUPPRESS';

8. Find source information for an atom.

SELECT a.* FROM mrsab a, mrconso b
WHERE b.aui = 'A10442923'
     AND a.rsab = b.sab
     AND a.curver = 'Y';

Last Reviewed: July 29, 2016