Even adding both RxNorm MIN class and RxNorm Extension existing ingredient combinations will not resolve the NDC issue completely. It looks like we need to add new ingredient combinations anyway.
Please find queries inside:
Among 82 NDC codes with 1-to-many mapping:
SELECT DISTINCT c1.concept_id
FROM concept c1
JOIN concept_relationship cr1
ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL
JOIN concept c2
ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL
WHERE c1.vocabulary_id = 'NDC'
GROUP BY c1.concept_id
HAVING count(DISTINCT c2.concept_id) > 1
ORDER BY c1.concept_id
There are just 16 with appropriate drug forms and likely MINs
with all_ndc as (
select c1.concept_id as ndc_id,
c1.concept_name as ndc_name,
array_agg(distinct ds.ingredient_concept_id order by ds.ingredient_concept_id) ndc_arr
from concept c1
join concept_relationship r on r.concept_id_1 = c1.concept_id and r.invalid_reason is null and r.relationship_id = 'Maps to'
join drug_strength ds on ds.drug_concept_id = r.concept_id_2
where c1.vocabulary_id = 'NDC'
and c1.concept_id in (SELECT DISTINCT c1.concept_id
FROM concept c1
JOIN concept_relationship cr1
ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL
JOIN concept c2
ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL
WHERE c1.vocabulary_id = 'NDC'
GROUP BY c1.concept_id
HAVING count(DISTINCT c2.concept_id) > 1
ORDER BY c1.concept_id
)
group by c1.concept_id, c1.concept_name
)
select distinct n.ndc_id, n.ndc_name, c3.concept_id as rx_id, c3.concept_name as rx_name, cl_drugs.form_id, cl_drugs.form_name
from all_ndc n
join (
select c1.concept_id as form_id,
c1.concept_name as form_name,
array_agg(ds.ingredient_concept_id order by ds.ingredient_concept_id) drugs_arr
from concept c1
join drug_strength ds on ds.drug_concept_id = c1.concept_id
where c1.vocabulary_id like 'Rx%'
and c1.concept_class_id = 'Clinical Drug Form'
group by c1.concept_id, c1.concept_name
) cl_drugs on cl_drugs.drugs_arr=n.ndc_arr
join concept_relationship r3 on n.ndc_id = r3.concept_id_1 and r3.invalid_reason is null and r3.relationship_id = 'Maps to'
join concept c3 on r3.concept_id_2 = c3.concept_id
order by 1,2,3,4,5,6
The rest 66 of them have no such. Most of them are multivitamins, but still
with all_ndc as (
select c1.concept_id as ndc_id,
c1.concept_name as ndc_name,
array_agg(distinct ds.ingredient_concept_id order by ds.ingredient_concept_id) ndc_arr
from concept c1
join concept_relationship r on r.concept_id_1 = c1.concept_id and r.invalid_reason is null and r.relationship_id = 'Maps to'
join drug_strength ds on ds.drug_concept_id = r.concept_id_2
where c1.vocabulary_id = 'NDC'
and c1.concept_id in (SELECT DISTINCT c1.concept_id
FROM concept c1
JOIN concept_relationship cr1
ON c1.concept_id = cr1.concept_id_1 AND cr1.relationship_id = 'Maps to' AND cr1.invalid_reason IS NULL
JOIN concept c2
ON cr1.concept_id_2 = c2.concept_id AND c2.standard_concept = 'S' AND c2.invalid_reason IS NULL
WHERE c1.vocabulary_id = 'NDC'
GROUP BY c1.concept_id
HAVING count(DISTINCT c2.concept_id) > 1
ORDER BY c1.concept_id
)
group by c1.concept_id, c1.concept_name
)
select distinct n.ndc_id, n.ndc_name, c3.concept_id as rx_id, c3.concept_name as rx_name, cl_drugs.form_id, cl_drugs.form_name
from all_ndc n
left join (
select c1.concept_id as form_id,
c1.concept_name as form_name,
array_agg(ds.ingredient_concept_id order by ds.ingredient_concept_id) drugs_arr
from concept c1
join drug_strength ds on ds.drug_concept_id = c1.concept_id
where c1.vocabulary_id like 'Rx%'
and c1.concept_class_id = 'Clinical Drug Form'
group by c1.concept_id, c1.concept_name
) cl_drugs on cl_drugs.drugs_arr=n.ndc_arr
join concept_relationship r3 on n.ndc_id = r3.concept_id_1 and r3.invalid_reason is null and r3.relationship_id = 'Maps to'
join concept c3 on r3.concept_id_2 = c3.concept_id
where form_id is null
order by 1,2,3,4,5,6
;
