Example SQL Queries and associated Java Code

Relationships

Basics

select * from concepts;
select * from relationship_types;
select * from concept_relationships ;
select * from concept_relationships_attributes limit 20;

Example Concept: Structure of left lower leg (SCTID: 48979004)

Note: Stated is identical to Inferred

in IHTSDO Browser 

Example in WCI Browser

Access the concept's relationships (where it is the source) via SQL

select * from concept_relationships where from_id = (select id from concepts where terminologyid ='48979004 ')

and obsolete = false
and inferred = false;

-- 3 (2 RelType = 'Is a' and 1 RelType = 'other' ... ID: '2960880')

List<ConceptRelationship> rels = con.getRelationships()

Access the "other" relationship information (with relationshipId : 2960880)

Get the Relationship Type

select * from concepts where terminologyid in (select additionalRelationshipType from concept_relationships where id = '2960880');

-- returns Laterality

--  Note: additionalRelationshipType is a snomed Id

Concept c = contentClientRestClient.getConcept(rel.getAdditionalRelationshipType(), terminology, version, null, authToken);

Get the Relationship Destination

select * from concepts where id in (select to_id from concept_relationships where id = '2960880'); 
-- returns  Left

--  Note: to_id is a concept id (i.e. not a SCTID)

Concept c = contentClientRestClient.rel.getTo();

Get the Characteristic Type

select inferred from concept_relationships where id = '2960880';

-- returns  0

--  Note: 1 means inferred

-- Note: There appears to also be a field 'stated' which should always be an inverse of the 'inferred' field as proven by fact that this returns zero values: select * from concept_relationships where inferred = stated and obsolete = false;

Boolean isInferred = rel.isInferred();

-- or Boolean isStated = rel.isStated()

-- or alternatively Boolean isStated = !rel.isInferred()

Get the Relationship Group

select relGroup from concepts where id in (select to_id from concept_relationships where id = '2960880');
-- returns  0

String group = rel.getGroup();