--  cleft palate / cleft lip

--  4/22/2012  Added palate, soft palate, hard palate
--  3/18/2013  Rerun (some table changes)



--             
select * from dev_litdb.uid 
where uid in ("D002972","D002971","D010159","D021362","D010160"); 

drop table cpCast;
create table cpCast as 
select uid, nm 
from dev_litdb.uid
where uid in ("D002972","D002971","D010159","D021362","D010160"); 

select * from cpCast; 

                       
drop table cp_eff00;
create table cp_eff00 as
select a.pmid, 
	a.uid,
	quid,
	evermajr,majr
from dev_litdb.mh_eff a, cpCast b
where b.uid=a.uid 
  and quid not in ("Q000601","Q000145"); 


drop table cp_pmid;
create table cp_pmid as
select pmid
from cp_eff00
group by pmid; 

--                                       B

drop table cp_chemtox;
create table cp_chemtox as
select a.pmid, 
	b.chemuid,
	b.quid,
	b.majr,
	b.evermajr
from cp_pmid a, dev_litdb.mh_chem b
where a.pmid=b.pmid
  and quid in ("Q000633", "Q000506", "Q000009"); 

drop table cp_pmidsmall;
create table cp_pmidsmall as
select pmid 
from cp_chemtox
group by pmid; 


drop table cp_arts;
create table cp_arts as
select a.pmid,
	b.pubyr,
	b.title
from cp_pmidsmall a, dev_litdb.pmid_master b
where a.pmid = b.pmid;