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

This diagram shows how to find all information associated with a particular atom (AUI value). Typically, when performing AUI searches, the most useful starting point is MRCONSO.RRF. This kind of search will yield all of the relevant fields of an atom. With this information you can search for data residing in other files that are connected to this atom. Note: as semantic types are concept properties, an atom's semantic type is found by searching for the semantic type of its concept.

When performing AUI searches in MRREL.RRF, the resulting rows will yield an AUI2 value that can be subsequently searched for in MRCONSO.RRF to identify the atom (and concept) on the other side of the relationship.

When performing AUI searches in MRHIER.RRF, the resulting rows will yield a PAUI (parent AUI) and a PTR (path to root). The PAUI is the "parent" of the AUI in this particular context. The PTR is a dot separated list of AUI values showing the entire path from the root node of the context tree. These AUI values can be subsequently searched in MRCONSO.RRF to identify the atom (and concept) involved.

When searching for connected information in tables with "STYPE" fields, be aware of the "level" of information you are interested in gathering. For strictly atom information you will want to restrict to STYPE = AUI.

 

Corresponding Oracle Queries:

1. Find an atom.

SELECT * FROM mrconso
WHERE aui = 'A0066368';

2. Find STYs associated with an atom's concept.

SELECT a.* FROM mrsty a, mrconso b
WHERE aui = 'A0066368'
     AND a.cui = b.cui;

3. Find definitions associated with an atom.

The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

SELECT * FROM mrdef a, mrconso b
WHERE b.aui = 'A0066368'
     AND a.aui = b.aui;

4. Find contexts associated with an atom.

The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

SELECT * FROM mrhier a, mrconso b
WHERE b.aui = 'A0066368'
     AND a.aui = b.aui;

5. Find attributes associated with an atom.

The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

STYPE will tell you exactly how the attribute is related to the ATOM, e.g., STYPE = AUI means an atom attribute.

SELECT * FROM mrsat a, mrconso b
WHERE b.aui = 'A0066368'
     AND a.metaui = b.aui;

6. Find relationships associated with an atom.

The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom (like the SAB) that may be useful.

STYPE2 will tell you exactly how the relationship is related to the ATOM, e.g., STYPE2 = AUI means an atom relationship.

Note: In MRREL, the REL/RELA always expresses the nature of the relationship from AUI2 to the "current concept", AUI1. Because we're joining AUI1 from MRREL to AUI from MRCONSO below, this represents the "natural" direction of the relationship.

SELECT * FROM mrrel a, mrconso b
WHERE b.aui = 'A0066368'
     AND a.aui1 = b.aui;

Last Reviewed: January 25, 2016