Postgres status
objects in Exp_pattern that have:
1- only life stage (exp_lifestage) 726 objects 2- only anatomy (exp_anatomy) 2749 objects 3- only GO (exp_goid) 952 objects 4- Life stage + anatomy 4568 5- Life stage + GO 78 6- Anatomy + GO 779 7- Life stage+ Anatomy + GO 854 8- No Life_stage NO GO NO Anatomy 652
total number of Expr objects in postgres 11683 (the sum of the above is 11358, talk to J to pull out the remaining)
1) objects that have only life stage: 726 results -725 without duplicated objects -> Check out the discrepancy
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
2) objects that have only anatomy: 3514 results, 2749 w/o duplicates
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
3) objects that have only go: 952 results, 951 w/o duplicates -> Check out the discrepancy SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) ORDER BY exp_name;
4) objects that have life stage and anatomy but not GO: 6169 results - 4568 without duplicates
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
5) objects that have life stage and GO but not anatomy: 78 results
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
6) objects that have anatomy and GO but not life_stage: 929 results - 779 without duplicates
SELECT * FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
7) objects that have anatomy and GO and life_stage: 1178 results - 854 without the duplicated objects
SELECT * FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
query to filter out duplicates SELECT DISTINCT(exp_name) FROM exp_name WHERE joinkey IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL) ORDER BY exp_name;
8) objects that have no anatomy no GO and no life_stage: 652 results
SELECT * FROM exp_name WHERE joinkey NOT IN (SELECT joinkey FROM exp_lifestage WHERE exp_lifestage IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_anatomy WHERE exp_anatomy IS NOT NULL) AND joinkey NOT IN (SELECT joinkey FROM exp_goid WHERE exp_goid IS NOT NULL);
Back to .ace examples