 ) q_bn on q_bn.concept_code_1=m.q_dcode
 left join (
   select r.concept_id_1, r.concept_id_2 
-  from devv5.concept_relationship r
-  join devv5.concept on concept_id=r.concept_id_2 and concept_class_id ='Brand Name'
+  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)

  join relationship_to_concept m on m.concept_code_1=r.concept_code_2 -- left join if not 
) q_df on q_df.concept_code_1=m.q_dcode 
left join (
  select r.concept_id_1, r.concept_id_2 from devv5.concept_relationship r
  join devv5.concept on concept_id=r.concept_id_2 and concept_class_id ='Dose Form' -- Dose Form of r
  where r.invalid_reason is null and r.relationship_id='RxNorm has dose form'
) r_df on r_df.concept_id_1=m.r_did
-- get Brand Name for q and r

  select drug_id as did, count(*) as cnt from r_drug_ing group by drug_id
;
-- Set all counts for Ingredient and Clinical Drug Comp to null, so in comparisons it can match whatever number necessary. Reason is that, like ingredients, Clinical Drug Comp is always only one ingredient
update r_ing_count set cnt=null where did in (select concept_id from concept where concept_class_id in ('Clinical Drug Comp', 'Ingredient'));

create index x_r_drug_ing on r_drug_ing(drug_id, ing_id) nologging;

