For a concept identifier entered as the input parameter, the query lists all existing relationships with other concepts. The resulting output includes:
- Type of relationship (including both relationship ID and description)
- Details of the other concept to which the relationship has been defined
- Polarity of the relationship
o Polarity of “Relates to” implies the input concept is the first concept or CONCEPT_ID_1 of the relationship
o Polarity of “Is Related by” implies the input concept is the second concept or CONCEPT_ID_2 of the relationship
In vocabulary Version 4.0 and above all relationships are bi-directional, ie. all relationships are repeated as a mirrored version, where CONCEPT_ID_1 and CONCEPT_ID_2 are swapped and the inverse relationship ID is provided.
Input:
Parameter |
Example |
Mandatory |
Notes |
Concept ID |
192671 |
Yes |
GI - Gastrointestinal hemorrhage |
As of date |
Sysdate |
No |
Valid record as of specific date. Current date – sysdate is a default |
Sample query run:
The following is a sample run of the query to extract all relationships of the input concept. The input parameters are highlighted in blue.
SELECT 'Relates to' relationship_polarity,
CR.relationship_ID,
RT.relationship_name,
D.concept_Id concept_id,
D.concept_Name concept_name,
D.concept_Code concept_code,
D.concept_Class concept_class,
D.vocabulary_id concept_vocab_ID,
VS.vocabulary_name concept_vocab_name
FROM vocabulary.concept_relationship CR,
vocabulary.concept A,
vocabulary.concept D,
vocabulary.vocabulary VA,
vocabulary.vocabulary VS,
vocabulary.relationship RT
WHERE CR.concept_id_1 = A.concept_id
AND A.vocabulary_id = VA.vocabulary_id
AND CR.concept_id_2 = D.concept_id
AND D.vocabulary_id = VS.vocabulary_id
AND CR.relationship_id = RT.relationship_ID
AND A.concept_id = 192671
AND sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date
UNION ALL
SELECT 'Is related by' relationship_polarity,
CR.relationship_ID,
RT.relationship_name,
A.concept_Id concept_id,
A.concept_name concept_name,
A.concept_code concept_code,
A.concept_class concept_class,
A.vocabulary_id concept_vocab_ID,
VA.Vocabulary_Name concept_vocab_name
FROM vocabulary.concept_relationship CR,
vocabulary.concept A,
vocabulary.concept D,
vocabulary.vocabulary VA,
vocabulary.vocabulary VS,
vocabulary.relationship RT
WHERE CR.concept_id_1 = A.concept_id
AND A.vocabulary_id = VA.vocabulary_id
AND CR.concept_id_2 = D.concept_id
AND D.vocabulary_id = VS.vocabulary_id
AND CR.relationship_id = RT.relationship_ID
AND D.concept_id = 192671
AND sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date
Output:
Output field list:
Field |
Description |
Relationship_Polarity |
Polarity of the relationship with the input concept as a reference:
- “Relates to”: Indicates input concept is CONCEPT_ID_1 or the first concept of the relationship
- “Is Related by”: Indicates input concept
|
Relationship_ID |
Identifier for the type of relationship |
Relationship_Name |
Name of the type of relationship |
Concept_ID |
Unique identifier of the concept related to the input concept |
Concept_Name |
Name of the concept related to the input concept |
Concept_Code |
Concept code of concept related to the input concept |
Concept_Class |
Concept Class of concept related to the input concept |
Concept_Vocab_ID |
ID of the vocabulary the related concept is derived from |
Concept_Vocab_Name |
Name of the vocabulary the related concept is derived from |
Sample output record 1:
Field |
Value |
Relationship_Polarity |
Is Related to |
Relationship_ID |
49 |
Relationship_Name |
Has finding site (SNOMED-CT) |
Concept_ID |
4046957 |
Concept_Name |
Gastrointestinal tract |
Concept_Code |
122865005 |
Concept_Class |
Body structure |
Concept_Vocab_ID |
1 |
Concept_Vocab_Name |
SNOMED-CT |
Sample output record 2:
Field |
Value |
Relationship_Polarity |
Is Related to |
Relationship_ID |
125 |
Relationship_Name |
MedDRA to SNOMED-CT equivalent (OMOP) |
Concept_ID |
35707864 |
Concept_Name |
Gastrointestinal haemorrhage |
Concept_Code |
10017955 |
Concept_Class |
Preferred Term |
Concept_Vocab_ID |
15 |
Concept_Vocab_Name |
MedDRA |
|
|