 
 commit;
 
-insert into pid_interaction_evidence(interaction_id, evidence_id) select distinct i.id, e.id from pid_interaction i, evidence e, zstg_pid_interactionreference z where z.interaction_id = i.pid_interaction_id and z.pubmed_id=e.pubmed_id and to_char(substr(e.sentence, 1, 4000))='Pathway publication';
+--insert into pid_interaction_evidence(interaction_id, evidence_id) select distinct i.id, e.id from pid_interaction i, evidence e, zstg_pid_interactionreference z where z.interaction_id = i.pid_interaction_id and z.pubmed_id=e.pubmed_id and to_char(substr(e.sentence, 1, 4000))='Pathway publication';
+
+
+insert into pid_interaction_evidence(interaction_id, evidence_id) select distinct i.id, e.id from pid_interaction i, evidence e, zstg_pid_interactionreference z, zstg_pid_reference_pubmed z2 where z.interaction_id = i.pid_interaction_id and z.pubmed_id= z2.reference_id and z2.pubmed_id = e.pubmed_id and to_char(substr(e.sentence, 1, 4000))='Pathway publication';
+
 commit;
 
 -- load into pid_entityName and pid_entityAccession

/

-- inserted rows..but the id does not help find any associated interactions..so what are these conditions for?
insert into pid_participant(condition_name, discriminator, pid_interaction_id) select distinct conditionname,'Condition' as discriminator, interaction_id from zstg_pid_interactioncondition;
commit;

-- Added on the basis of the May 09 dump
update pid_participant p set p.CONDITION_NAME = (select distinct conditionname from zstg_pid_interactioncondition x 
where p.PID_PHYSICALENTITY_ID = x.INTERACTION_ID) where condition_name is null and p.discriminator = 'Condition';
commit;



commit; 

-- association between SmallMoleculeEntity and Agent
--insert into pid_entity_agent(physical_entity_id, agent_id) select distinct p.ID, g.AGENT_ID from pid_physical_entity p, pid_entity_name a , agent g where p.PID_PHYSICALENTITY_ID = a.PID_PHYSICALENTITY_ID and p.DISCRIMINATOR = 'SmallMoleculeEntity' and lower(trim(a.NAME)) = lower(trim(g.AGENT_NAME));
--commit;


-- See if one can add agents on the basis of Unigene cluster number
