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

This diagram shows how to find all information associated with a particular UMLS concept (CUI value). The information returned could be compiled into a "concept report" showing an overview of what the UMLS concept contains.

Certain files contain both AUI (Atom Unique Identifier) and CUI (Concept Unique Identifier) fields. In these cases, a CUI search will yield results for all atoms that exist within that concept. For example, a CUI search in MRDEF.RRF will yield all definitions associated with all atoms (AUIs) within the concept having that CUI.

When performing CUI searches in MRREL.RRF, the resulting rows will yield a CUI2 value that is searchable in MRCONSO.RRF. That search will identify the concept on the other side of the relationship.

Corresponding Oracle Queries:

1. Find all atoms of a UMLS concept.

SELECT * FROM mrconso
WHERE cui = 'C0032344';

2. Find all source definitions associated with a UMLS concept.

SELECT * FROM mrdef
WHERE cui = 'C0032344';

3. Find all source contexts associated with a UMLS concept.

SELECT * FROM mrhier
WHERE cui = 'C0032344';

4. Find all attributes for a UMLS concept.

SELECT * FROM mrsat
WHERE cui = 'C0032344'
     AND stype = 'CUI';

5. Find all semantic types for a UMLS concept.

SELECT * FROM mrsty
WHERE cui = 'C0032344';

6.a. Find all relationships for a UMLS concept.
Note: In MRREL, the REL/RELA always expresses the nature of the relationship from CUI2 to the "current concept", CUI1. Because we're querying CUI1 below, this represents the "natural" direction of the relationship.

SELECT * FROM mrrel
WHERE cui1 = 'C0032344'

6.b. Find all inverse relationships for a UMLS concept.
Note: In MRREL, the REL/RELA always expresses the nature of the relationship from CUI2 to the "current concept", CUI1. Because we're querying CUI2 below, this represents the opposite of the "natural" direction of the relationship.

SELECT * FROM mrrel
WHERE cui2 = 'C0032344';
     AND stype2 = 'CUI';

7. Find all relationships for a concept and the preferred (English) name of the CUI2.

SELECT a.cui1, a.cui2, b.str FROM mrrel a, mrconso b
WHERE a.cui1 = 'C0032344'
     AND a.stype1 = 'CUI'
     AND a.cui2 = b.cui
     AND b.ts = 'P'
     AND b.stt = 'PF'
     AND b.ispref = 'Y'
     AND b.lat = 'ENG';

Last Reviewed: January 25, 2016