**** These are the temporary tables used to gather supplementary data for the tomato unigene build; the current file can be seen at ftp://sgn.cornell.edu/unigene_builds/Tomato_200607_build_1_data/ ****

------------------------

create temporary table temp_unigene_est_23 as select unigene_id, est_id from unigene_member join unigene using (unigene_id) where unigene_build_id=23;

create temporary table temp_unigene_est_18 as select unigene_id, est_id from unigene_member join unigene using (unigene_id) where unigene_build_id=18;

create temporary table temp_join_18_23 as select distinct temp_unigene_est_23.unigene_id as SGN_3xxxxx, temp_unigene_est_18.unigene_id as SGN_2xxxxx from temp_unigene_est_18 join temp_unigene_est_23 using (est_id) order by SGN_3xxxxx;

------------------------

create temporary table temp_at as select distinct apply_id, target_db_id, evalue, score from blast_hits join blast_annotations using (blast_annotation_id) where target_db_id like 'At%' and apply_id between 212544 and 243121;

create temporary table temp_at_max as select apply_id, max(score) as score from temp_at group by apply_id;

create temporary table temp_at_max_min as select apply_id, score, min(evalue) as evalue from temp_at_max join temp_at using (apply_id, score) group by apply_id, score;

create temporary table temp_at_best as select apply_id, score, target_db_id, evalue from temp_at join temp_at_max_min using (apply_id, score, evalue) order by apply_id;

create temporary table temp_col_1_2_8_9 as select SGN_3xxxxx, SGN_2xxxxx, target_db_id as best_At_hit, score as At_score, evalue as At_evalue from temp_at_best full outer join temp_join_18_23 on (apply_id=SGN_2xxxxx) order by SGN_3xxxxx;

------------------------

create temporary table temp_gi as select distinct apply_id, target_db_id, evalue, score from blast_hits join blast_annotations using (blast_annotation_id) where target_db_id like 'gi%' and apply_id between 212544 and 243121;

create temporary table temp_gi_max as select apply_id, max(score) as score from temp_gi group by apply_id;

create temporary table temp_gi_max_min as select apply_id, score, min(evalue) as evalue from temp_gi_max join temp_gi using (apply_id, score) group by apply_id, score;

create temporary table temp_gi_best as select apply_id, score, target_db_id, evalue from temp_gi join temp_gi_max_min using (apply_id, score, evalue) order by apply_id;

create temporary table temp_col_1_2_12_13 as select SGN_3xxxxx, SGN_2xxxxx, target_db_id as best_gi_hit, score as gi_score, evalue as gi_evalue from temp_gi_best full outer join temp_join_18_23 on (apply_id=SGN_2xxxxx) order by SGN_3xxxxx;

------------------------

create temporary table temp_go_interpro as select go_accession, interpro_accession from go join interpro_go using (go_accession);

create temporary table temp_interpro_unigene as select distinct unigene_id, interpro_id from domain join domain_match using (domain_id) where unigene_id between 212544 and 243121;

create temporary table temp_interpro_unigene_id as select distinct unigene_id, interpro_accession from temp_interpro_unigene join interpro using (interpro_id);

create temporary table temp_go_interpro_unigene as select unigene_id, interpro_accession, go_accession from temp_go_interpro join temp_interpro_unigene_id using (interpro_accession) order by unigene_id;

create temporary table temp_col_1_2_3_thru_6 as select SGN_3xxxxx, SGN_2xxxxx, interpro_accession, go_accession from temp_go_interpro_unigene full outer join temp_join_18_23 on (unigene_id=SGN_2xxxxx) order by SGN_3xxxxx;

------------------------

create temporary table temp_col_1_2_8_9_12_13 as select * from temp_col_1_2_8_9 full outer join temp_col_1_2_12_13 using (SGN_3xxxxx, SGN_2xxxxx) order by SGN_3xxxxx;

create temporary table temp_col_1_2_3_thru_6_8_9_12_13 as select * from temp_col_1_2_8_9_12_13 full outer join temp_col_1_2_3_thru_6 using (SGN_3xxxxx, SGN_2xxxxx) order by SGN_3xxxxx;
