General Queries‎ > ‎

G09: Find descendants for a given concept

For a concept identifier entered as the input parameter, this query lists all descendants in the hierarchy of the domain. Descendant are concepts have a relationship to the given concept that is defined as hierarchical in the relationship table, and any secondary, tertiary etc. concepts going down in the hierarchy. The resulting output provides the descendant concept details and the minimum and maximum level of separation.

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  C.concept_id     as descendant_concept_id,
        C.concept_name   as descendant_concept_name,
        C.concept_code   as descendant_concept_code,
        C.concept_class  as descendant_concept_class,
        C.vocabulary_id,
        VA.vocabulary_name,
        A.min_levels_of_separation,
        A.max_levels_of_separation
FROM    vocabulary.concept_ancestor A,
        vocabulary.concept          C,
        vocabulary.vocabulary       VA
WHERE   A.descendant_concept_id = C.concept_id
AND     C.vocabulary_id = VA.vocabulary_id
AND     A.ancestor_concept_id <> A.descendant_concept_id
AND     A.ancestor_concept_id = 192671
AND     sysdate BETWEEN valid_start_date AND valid_end_date
ORDER BY 5,7
Output:
Output field list:
 Field  Description
 Descendant_Concept_ID  Unique identifier of the concept related to the descendant concept
 Descendant_Concept_Name  Name of the concept related to the descendant concept
 Descendant_Concept_Code  Concept code of concept related to the descendant concept
 Descendant_Concept_Class  Concept Class of concept related to the descendant concept
 Vocabulary_ID  ID of the vocabulary the descendant concept is derived from
 Vocabulary_Name;  Name of the vocabulary the descendant concept is derived from
 Min_Levels_of_Separation  The length of the shortest path between the concept and the descendant
 Max_Levels_of_Separation  The length of the longest path between the concept and the descendant

Sample output record:
 Field  Value
 Descendant_Concept_ID  4318535
 Descendant_Concept_Name  Duodenal haemorrhage
 Descendant_Concept_Code  95533003
 Descendant_Concept_Class  Clinical finding
 Vocabulary_ID  1
 Vocabulary_Name  SNOMED-CT
 Min_Levels_of_Separation  1
 Max_Levels_of_Separation  1
Comments