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

[Jim Busser]

On 2010-02-19, at 3:31 AM, Karsten Hilbert wrote:

> SELECT *
> FROM ...
> WHERE
>       ...
> 
>               and
> 
>       CURRENT_STATUS_FLAG = the_value_of_records_of_interest
> ;

but the problem is that there exists no one single date that is in common to 
all drug records... their current status as determined by the date on which the 
manufacturer happened to supply the status information. See screenshot. The 
table has 48,736 entries for 17,523 drugs.


> 
>> Below is where I got stuck:
> 
> How ?
> 
>> 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 …
> 
> See my other post.
> 
>> ... also would the above fail if extended across >2 tables?
> 
> Not sure I understand what you mean by "extend".

On account of the above (attempted) selection drawing from 4 tables, apparently 
you have to set a condition that will result in one record per desired entity, 
which is why a person would above specify that for each drug in the first 
table, you want one matching value in a related table (otherwise you get a 
Cartesian cross-product such that if you had 4 records in source table A1, and 
4 records in source table A2, you would end up with 16 records INSERTed INTO.

So I did not know whether in doing the SELECT FROM, it would be proper and 
supported to try a statement like
        WHERE A1.DRUG_CODE = A2.DRUG_CODE AND A1.DRUG_CODE=A3.DRUG_CODE AND 
A1.DRUG_CODE=A4.DRUG_CODE

-- Jim