Difference between revisions of "Postgres status"
| (One intermediate revision by the same user not shown) | |||
| 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 25: | Line 25: | ||
| − | 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 31: | 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; | ||
| + | |||
| + | |||
3) objects that have only go: 952 results, 951 w/o duplicates -> Check out the discrepancy | 3) objects that have only go: 952 results, 951 w/o duplicates -> Check out the discrepancy | ||
| Line 38: | Line 41: | ||
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; | 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 | + | |
| + | |||
| + | 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); | 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 45: | Line 50: | ||
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; | 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 | + | |
| + | |||
| + | 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 52: | Line 59: | ||
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; | 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 | + | |
| + | |||
| + | 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); | 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 58: | 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 66: | 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; | ||
| + | |||
| + | |||
8) objects that have no anatomy no GO and no life_stage: 652 results | 8) objects that have no anatomy no GO and no life_stage: 652 results | ||
| Line 73: | Line 85: | ||
| − | + | Back to [[.ace examples]] | |
| − | |||
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
