Drug Queries‎ > ‎

D06: Find branded drugs by ingredient

This query is designed to extract all branded drugs that have a specified ingredient. The query accepts an ingredient concept ID as the input and returns all branded 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’.

 Parameter  Example  Mandatory  Notes
 Ingredient Concept ID  966991  Yes Concept ID for ‘Simethicone’.
 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 branded 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_concept_name,
        A.concept_code       Ingredient_concept_code,
        A.concept_class      Ingredient_concept_class,
        D.concept_id         branded_drug_id,
        D.concept_name       branded_drug_name,
        D.concept_code       branded_drug_concept_code,
        D.concept_class      branded_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    lower(D.concept_class)   = 'branded drug'
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 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
 Branded_Drug_ID  Concept ID of branded drug with the ingredient
 Branded_Drug_Name  Name of branded drug concept with the ingredient
 Branded_Drug_Concept_Code  Concept code of the branded drug with the ingredient
 Branded_Drug_Concept_Class  Concept class of branded drug with the ingredient

Sample output record 1: Single ingredient branded drug
 Field  Value
 Ingredient_Concept_ID  966991
 Ingredient_name  Simethicone
 Ingredient_Concept_code  9796
 Ingredient_Concept_class  Ingredient
 Branded_Drug_ID  19132733
 Branded_Drug_Name  Simethicone 66.7 MG/ML Oral Suspension [Mylicon]
 Branded_Drug_Concept_Code  809376
 Branded_Drug_Concept_Class  Branded Drug

Sample output record 2: Combination branded drug or pack
 Field  Value
 Ingredient_Concept_ID  966991
 Ingredient_name  Simethicone
 Ingredient_Concept_code  9796
 Ingredient_Concept_class  Ingredient
 Branded_Drug_ID  993537
 Branded_Drug_Name  Aluminum Hydroxide 40 MG/ML / Magnesium Hydroxide 40 MG/ML / Simethicone 4 MG/ML Oral Suspension [Mylanta]
 Branded_Drug_Concept_Code  351585
 Branded_Drug_Concept_Class  Branded Drug