Difference between revisions of "Postgres status"
Line 17: | Line 17: | ||
− | 1) objects that have only life stage: 726 results -725 without | + | 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); | 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); | ||
Line 26: | Line 26: | ||
− | 2) objects that have only anatomy: 3514 results, 2749 w/o | + | 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); | 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); | ||
Line 32: | Line 32: | ||
query to filter out duplicates | 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; | 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; | ||
− | |||
Line 44: | Line 43: | ||
− | + | 4) objects that have life stage and anatomy but not GO: 6169 results - 4568 without duplicates | |
− | 4) objects that have life stage and anatomy but not GO: 6169 results - 4568 without | ||
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); | 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); | ||
Line 54: | Line 52: | ||
− | + | 5) objects that have life stage and GO but not anatomy: 78 results | |
− | 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); | 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); | ||
Line 64: | Line 61: | ||
− | + | 6) objects that have anatomy and GO but not life_stage: 929 results - 779 without duplicates | |
− | 6) objects that have anatomy and GO but not life_stage: 929 results - 779 without | ||
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); | 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); | ||
Line 71: | Line 67: | ||
query to filter out duplicates | 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; | 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; | ||
− | |||
Line 81: | Line 76: | ||
query to filter out duplicates | 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; | 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; | ||
− | |||
Latest revision as of 13:06, 5 May 2014
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