 select drug.concept_code as drug_code, nvl(ing.concept_id, 0) as ing_id, i.concept_code as ing_code -- if ingredient is not mapped use 0 to still get the right ingredient count
 from drug_concept_stage i
 left join relationship_to_concept r1 on r1.concept_code_1=i.concept_code
-left join devv5.concept ing on ing.concept_id=r1.concept_id_2 -- link standard ingredients to existing ones
+left join concept ing on ing.concept_id=r1.concept_id_2 -- link standard ingredients to existing ones
 join internal_relationship_stage r2 on r2.concept_code_2=i.concept_code
 join drug_concept_stage drug on drug.concept_class_id not in ('Unit', 'Ingredient', 'Brand Name', 'Non-Drug Prod', 'Dose Form', 'Device', 'Observation') -- include only drug product concept classes
   and drug.concept_code=r2.concept_code_1

-- Create table containing ingredients for each drug
create table r_drug_ing nologging as
  select de.concept_id as drug_id, an.concept_id as ing_id
  from devv5.concept_ancestor a 
  join devv5.concept an on a.ancestor_concept_id=an.concept_id and an.concept_class_id='Ingredient' 
    and an.vocabulary_id in ('RxNorm') -- to be expanded as new vocabs are added
  join devv5.concept de on de.concept_id=a.descendant_concept_id  
    and de.vocabulary_id in ('RxNorm')
;
-- Remove unparsable Albumin products that have no drug_strength entry: Albumin Human, USP 1 NS

) q_bn on q_bn.concept_code_1=m.q_dcode
left join (
  select r.concept_id_1, r.concept_id_2 
  from concept_relationship r
  join concept on concept_id=r.concept_id_2 and concept_class_id ='Brand Name'
  where r.invalid_reason is null 
) r_bn on r_bn.concept_id_1=m.r_did and m.rc_cnt is not null -- only take Brand Names if they don't come from Ingredients or Clinical Drug Comps
-- remove comments if mapping should be done both upwards (standard) and downwards (to find a possible unique low-granularity solution)
