Chado query HOWTO: Annotation ID -> UniProtKB ID

[Josh Goodman]

This query returns a 3 colum list containing the current FBgn ID, the Gene Annotation ID, and the UniProtKB ID. Instructions for connecting to our public Chado database can be found here.

Code: Select all

select f.uniquename, dbx_cg.accession as annotation_id, dbx_uniprot.accession as uniprot
        from feature f, feature_dbxref fdbx_uniprot, dbxref dbx_uniprot, db db_uniprot,
             feature_dbxref fdbx_cg, dbxref dbx_cg, db db_cg, cvterm cvt

        where upper(db_uniprot.name) like 'UNIPROT%' and upper(db_cg.name) = 'FLYBASE ANNOTATION IDS' and
              fdbx_uniprot.is_current = true and fdbx_cg.is_current = true and
              f.uniquename ~ '^FBgn[0-9]+$' and f.is_obsolete=false and f.is_analysis=false and cvt.name='gene' and
              f.feature_id=fdbx_uniprot.feature_id and fdbx_uniprot.dbxref_id=dbx_uniprot.dbxref_id and
              dbx_uniprot.db_id=db_uniprot.db_id and f.feature_id=fdbx_cg.feature_id and
              fdbx_cg.dbxref_id=dbx_cg.dbxref_id and dbx_cg.db_id=db_cg.db_id and f.type_id=cvt.cvterm_id;