User:Lindenb/Notebook/UMR915/20100705

=compute the difference between SLS2 and me=

create temporary table user_anonymous.T1 	(  	id int unsigned  primary key auto_increment,  	chrom varchar(20) not null,  	position int unsigned,  	sample_id int unsigned,index(sample_id),  	index (chrom),index(position),index(chrom,position)  	); insert into user_anonymous.T1(chrom,position,sample_id) select distinct V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 	where V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="sls2" ; create temporary table user_anonymous.T2  	(  	id int unsigned  primary key auto_increment,  	chrom varchar(20) not null,  	position int unsigned,  	sample_id int unsigned,index(sample_id),  	index (chrom),index(position),index(chrom,position)  	); insert into user_anonymous.T2(chrom,position,sample_id) select V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 where C1.snp_quality>=25 and C1.depth>4 and C1.depth<=1200 and C1.rms_quality >=25 and V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="lindenb" ; create temporary table user_anonymous.T3  	(  	chrom varchar(20) not null,  	position int unsigned,  	sample_id int unsigned,index(sample_id),  	prediction varchar(20),index(prediction),  	t1 int,  	t2 int,  	index (chrom),index(position),index(chrom,position)  	); insert into user_anonymous.T3(chrom,position,sample_id,prediction,t1,t2) select V.chrom, V.position, C.sample_id, P.prediction, user_anonymous.T1.id, user_anonymous.T2.id from ( variation as V,  polyphen as P,  vcf_call as C  ) left join user_anonymous.T1 on (T1.chrom=V.chrom and user_anonymous.T1.position=V.position and C.sample_id=T1.sample_id) left join user_anonymous.T2 on (T2.chrom=V.chrom and user_anonymous.T2.position=V.position and C.sample_id=T2.sample_id) where V.ref!='*' and length(P.alt)=1 and C.variation_id=V.id and P.variation_id=V.id and P.library='HumDiv' group by 1,2,3,4 ; delete from user_anonymous.T3 where t1 is null and t2 is null; select * from user_anonymous.T3  limit 200; select user_anonymous.T3.prediction as "pph2.HumDiv", concat("XXX",user_anonymous.T3.sample_id) as "Sample", COUNT(t1) as "sls2", COUNT(t2) as "pierre", COUNT(t1)-COUNT(t2) as "diff(sls2-pierr)" from ( user_anonymous.T3  ) group by  user_anonymous.T3.sample_id, user_anonymous.T3.prediction order by 1,2 ; =compute data found by me but not by sls2=

create temporary table user_anonymous.T1 	(  	id int unsigned  primary key auto_increment,  	chrom varchar(20) not null,  	position int unsigned,  	sample_id int unsigned,index(sample_id),  	index (chrom),index(position),index(chrom,position)  	); insert into user_anonymous.T1(chrom,position,sample_id) select distinct V1.chrom,V1.position,C1.sample_id from variation as V1 , vcf_call as C1, vcf_input as I1 	where V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="sls2" ; select S.name, V1.chrom, V1.position+1, V1.ref, V1.alt from ( 	variation as V1,  	vcf_call as C1,   	vcf_input as I1,  	sample as S  	) left join user_anonymous.T1 on (T1.chrom=V1.chrom and T1.position=V1.position and S.id=user_anonymous.T1.sample_id) where T1.id is null and C1.sample_id=S.id and C1.snp_quality>=25 and C1.depth>4 and C1.depth<=1200 and C1.rms_quality >=25 and V1.ref!='*' and length(V1.alt)=1 and C1.variation_id=V1.id and C1.input_id=I1.id and I1.creator="MOIMOIMOI" ;