Re: [Gnumed-devel] Getting Canadian drug data into FreeDiams

[Jim Busser]

Do you want to know something remarkable? I actually think I am close to done 
(including scripts) for the current structure of FreeDiams, I only just still 
have this problem populating the CIS table, as mapped at

        http://wiki.gnumed.de/bin/view/Gnumed/FreeDiams

because it wants only a single record defining the drug's availability, whereas 
Health Canada data provides a historical record of notices. Accordingly there 
exist multiple values of

        CURRENT_STATUS_FLAG

with varying ages of records, per drug. Presumably I need to make the SELECT 
command ignore values *except* for the newest per drug else some way to first 
delete the non-newest records per drug.

Below is where I got stuck:


INSERT INTO CIS (CIS, DENOMINATION, FORME, ADMINISTRATION, COMMERCIALISATION)
SELECT A1.DRUG_CODE, A1.BRAND_NAME, A2.PHARMACEUTICAL_FORM, 
A3.ROUTE_OF_ADMINISTRATION, A4.CURRENT_STATUS_FLAG
FROM drug A1, form A2, route A3, status A4
WHERE A1.DRUG_CODE = A2.DRUG_CODE …

... also would the above fail if extended across >2 tables?
... does such a select need primary or other keys (indexing) ?