Intermediate table from FDA NDC Class -> RxNorm ingredient

[jrlegrand]

Proposal

Something like this:

select distinct
	cls.class_type
	, trim(cls.class_name) as class_name
	, cpi.ingredient_rxcui
	, cpi.ingredient_name
	, cpi.ingredient_tty
from staging.stg_fda_ndc__class cls
left join staging.stg_fda_ndc__ndc ndc
	on cls.productid = ndc.productid
left join intermediate.int_rxnorm_clinical_products_to_ndcs ndccp
	on ndc.ndc11 = ndccp.ndc
left join intermediate.int_rxnorm_clinical_products_to_ingredients cpi
	on ndccp.clinical_product_rxcui = cpi.clinical_product_rxcui
where trim(cls.class_name) = 'Angiotensin-converting Enzyme Inhibitors'
	and cpi.ingredient_tty = 'IN'
order by cpi.ingredient_name asc

Rationale

If we could just use RxClass we wouldn't need to do this. In lieu of that, it might be useful to do this. As I was writing this, I was wondering how RxClass takes the class from DailyMed and converts it to the ingredient-level. Maybe in DailyMed it's at the ingredient level? In FDA, it's at the product-level which makes it hard to be certain that it's a class for ingredient A vs ingredient B... which is why I just limited this to single-ingredient products as you can see in the query above.