TRUNCATE table MedicinesATC;

INSERT INTO MedicinesATC (TradeID, ATC_Code) 
   SELECT DISTINCT T.ID as TradeID, D.ATC_Code
   From AtoZofMeds.TradeNames T inner join AtoZofMeds.ATCtoTradeXMap A on T.ID = A.TradeID 
   inner join AtoZofMeds.Drugs D on A.DrugID = D.ID 
   inner join AtoZofMeds.Drug_Status DS on T.Status = DS.Code and DS.fk_table = 'Tradenames'
   INNER JOIN AtoZofMeds.Drug_Status DS2 on D.Status = DS2.Code and DS2.fk_table = 'Drugs' AND DS2.published != 0
   where DS.Published != 0
 UNION
   SELECT DISTINCT T.ID as TradeID, D.ATC_Code
   From AtoZofMeds.TradeNames T inner join AtoZofMeds.Virtual_Products VP ON T.VPID = VP.ID
   INNER JOIN AtoZofMeds.ATCtoVPXmap X ON VP.ID = X.VPID
   INNER JOIN AtoZofMeds.Drugs D ON X.DrugID =D.ID
   inner join AtoZofMeds.Drug_Status DS on T.Status = DS.Code and DS.fk_table = 'Tradenames'
   INNER JOIN AtoZofMeds.Drug_Status DS2 on D.Status = DS2.Code and DS2.fk_table = 'Drugs' AND DS2.published != 0
   where DS.Published != 0;
   


