Drug Queries‎ > ‎

D04: Find drugs by ingredient

This query is designed to extract all drugs that contain a specified ingredient. The query accepts an ingredient concept ID as the input and returns all drugs that have the ingredient. It should be noted that the query returns both generics that have a single ingredient (i.e. the specified ingredient) and those that are combinations which include the specified ingredient.
The query requires the ingredient concept ID as the input. A list of these ingredient concepts can be extracted by querying the concept table for concept class of ‘Ingredient’, e.g. using query D02.

Input:
 Parameter  Example  Mandatory  Notes
 Ingredient Concept ID  966991  Yes Concept ID for ‘Simethicone’.
Ingredient concepts can be extracted from CONCEPT table as records of concept class of ‘Ingredient’
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run: 
The following are sample runs of the query to extract all generic drugs that contain an ingredient the concept ID for which is entered as input. The input parameters are highlighted in blue.
SELECT  A.concept_id              Ingredient_concept_id,
        A.concept_Name            Ingredient_name,
        A.concept_Code            Ingredient_concept_code,
        A.concept_Class           Ingredient_concept_class,
        D.concept_id              Drug_concept_id,
        D.concept_Name            Drug_name,
        D.concept_Code            Drug_concept_code,
        D.concept_Class           Drug_concept_class
FROM   vocabulary.concept_ancestor CA,
       vocabulary.concept          A,
       vocabulary.concept          D
WHERE  CA.ancestor_concept_id   = 966991 
AND    CA.ancestor_concept_id   = A.concept_id
AND    CA.descendant_concept_id = D.concept_id
AND    sysdate  BETWEEN A.valid_start_date AND A.valid_end_date
AND    sysdate  BETWEEN D.valid_start_date AND D.valid_end_date
Output:
Output field list:
 Field  Description
 Ingredient_Concept_ID  Concept ID of the ingredient entered as input
 Ingredient_name  Name of the ingredient
 Ingredient_Concept_code  Concept code of the ingredient
 Ingredient_Concept_class  Concept Class of the ingredient
 Generic_Concept_ID  Concept ID of drug with the ingredient
 Generic_Name  Name of drug concept with the ingredient
 Generic_Concept_Code  Concept code of the drug with the ingredient
 Generic_Concept_Class  Concept class of drug with the ingredient

Sample output record 1: Single ingredient generic
 Field  Value
 Ingredient_Concept_ID  966991
 Ingredient_name  Simethicone
 Ingredient_Concept_code  9796
 Ingredient_Concept_class  Ingredient
 Generic_Concept_ID  967306
 Generic_Name  Simethicone 10 MG/ML Oral Solution
 Generic_Concept_Code  251293
 Generic_Concept_Class  Clinical Drug

Sample output record 2: Combination generic
 Field  Value
 Ingredient_Concept_ID  966991
 Ingredient_name  Simethicone
 Ingredient_Concept_code  9796
 Ingredient_Concept_class  Ingredient
 Generic_Concept_ID  19082312
 Generic_Name  Aluminum Hydroxide 200 MG / Magnesium Hydroxide 200 MG / Simethicone 20 MG Chewable Tablet
 Generic_Concept_Code  317055
 Generic_Concept_Class  Clinical Drug
Comments