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

This diagram shows how to find all information associated with a particular source concept (SCUI value). This kind of search will yield all of the information about a source concept, from the source's point of view. The first search, in MRCONSO.RRF, will yield information about all of the atoms contained within that source concept. Further searches in the other files will yield information about relationships, attributes, and hierarchies.

When performing searches in MRREL.RRF, the resulting rows will yield AUI2 values that can be subsequently searched for in MRCONSO.RRF to identify the atom (and source concept) on the other side of the relationship. To find the source concept on the other side use the SCUI value from MRCONSO.RRF.

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

As of the UMLS 2008AB Release, contexts are always expressed in terms of AUI values. Thus, when searching for the AUIs of a SNOMED CT SCUI in MRHIER.RRF, you will find all of the tree positions of those AUIs in SNOMED CT. If a source, like SNOMED CT , actually expresses its hierarchies in terms of SCUI values and not AUI values, then the AUI chosen in MRHIER.RRF to carry context information will be the "preferred atom" of that source concept (SCUI).

Corresponding Oracle Queries:

1. Find all atoms (descriptions) for a SNOMED CT concept (SCUI).

SELECT * FROM mrconso
WHERE scui = '7947003'
     AND sab = 'SNOMED CT';

2. Find all definitions connected to SNOMED CT atoms (descriptions) for a SNOMED CT concept.

As of 2008AB, SNOMED CT does not yet contain any textual definitions.

SELECT a.* FROM mrdef a, mrconso b
WHERE b.scui = '7947003'
     AND b.sab = 'SNOMED CT'
     AND a.aui = b.aui;

3. Find all tree positions for SNOMED CT concepts.

SELECT a.* FROM mrhier a, mrconso b
WHERE b.scui = '7947003'
     AND b.sab = 'SNOMED CT'
     AND a.aui = b.aui;

4.a. Find all SNOMED CT relationships associated with a SNOMED CT concept (SCUI).
Note: In MRREL, the REL/RELA always expresses the nature of the relationship from the AUI2 to the "current concept", AUI1. Because we are joining the AUI field from MRCONSO to AUI1 from MRREL below, we could say that this query represents the "natural" direction of the relationship.

SELECT a.* FROM mrrel a, mrconso b
WHERE b.scui = '7947003'
     AND b.sab = 'SNOMED CT'
     AND a.aui1 = b.aui
     AND a.stype1 = 'SCUI'
     AND a.dir = 'Y';

4.b.
Note: In MRREL, the REL/RELA always expresses the nature of the relationship from the AUI2 to the "current concept", AUI1. Because we are joining the AUI field from MRCONSO to AUI2 from MRREL below, we could say that this query represents the opposite of the "natural" direction of the relationship.

SELECT a.* FROM mrrel a, mrconso b
WHERE b.scui = '7947003'
     AND b.sab = 'SNOMED CT'
     AND a.aui2 = b.aui
     AND a.stype1 = 'SCUI'
     AND a.dir = 'Y';

5. Find all attributes for a SNOMED CT concept (SCUI).

SELECT a.* FROM mrsat a, mrconso b
WHERE b.scui = '7947003'
     AND b.sab = 'SNOMED CT'
     AND a.metaui = b.aui
     AND stype = 'SCUI';

Last Reviewed: July 29, 2016