UMLS Database Query Diagrams:
How to perform searches for subset metadata concepts

This diagram shows how to perform searches for subset metadata concepts. Any source asserted subset within the UMLS has a metadata concept within the UMLS with TTY = 'SB'. These concepts are associated with a variety of attributes (found in MRSAT.RRF) that provide the metadata.

Corresponding Oracle Queries:

1. Find atoms of the "US English Dialect Subset" subset metadata concept.

SELECT * FROM mrconso
WHERE sab = 'SNOMEDCT'
     AND tty = 'SB'
     AND str = 'US English Dialect Subset';

2. Find semantic type of the "US English Dialect Subset" subset metadata concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom that may be useful.

SELECT * FROM mrconso a, mrsty b
WHERE a.sab = 'SNOMEDCT'
     AND a.tty = 'SB'
     AND a.str = 'US English Dialect Subset'
     AND a.cui = b.cui;

3. Find attributes of the "US English Dialect Subset" subset metadata concept.
The join on MRCONSO.RRF is not necessary, but it provides access to fields of the atom that may be useful.

SELECT * FROM mrconso a, mrsat b
WHERE a.sab = 'SNOMEDCT'
     AND a.tty = 'SB'
     AND a.str = 'US English Dialect Subset'
     AND a.cui = b.cui;

4. Find atoms that are subset members of the "US English Dialect Subset" subset metadata concept (using SUBSETMEMBER attribute).

SELECT c.* FROM mrconso a, mrsat b, mrconso c
WHERE a.sab = 'SNOMEDCT'
     AND a.tty = 'SB'
     AND a.str = 'US English Dialect Subset'
     AND b.atn = 'SUBSETMEMBER'
     AND b.atv like a.code || '%'
     AND b.metaui = c.aui
     AND b.sab = 'SNOMEDCT';

Last Reviewed: July 29, 2016