[ensembl-dev] MySQL query to retrieve protein annotation

[mag]

Hi Charlie,

You need to add a join between the protein_feature and translation 
tables, something like protein_feature.translation_id = 
translation.translation_id

Additionally, it is not recommended to use internal ids for querying, as 
these are not guaranteed to remain the same from on release to another.
You can use the stable id instead, which will refer to the same Ensembl 
feature across releases.

For example
SELECT protein_feature.hit_name, protein_feature.hit_start, 
protein_feature.hit_end, protein_feature.seq_start, 
protein_feature.seq_end, protein_feature.evalue, 
protein_feature.perc_ident, protein_feature.hit_description, 
protein_feature.translation_id FROM protein_feature, translation WHERE 
protein_feature.translation_id = translation.translation_id and 
stable_id = 'ENSMUSP00000081003' ;

You can find a more detailed description of the database schema here: 
http://www.ensembl.org/info/docs/api/core/core_schema.html


Hope that helps,
mag

On 22/02/2017 16:43, Charles Joseph Murphy wrote:
> Hi,
>
> I’ am trying to use the Ensembl public MySQL query to retrieve protein annotation for all of a genome’s genes. While trying to figure out the right MySQL queries to make, I’ am testing it out with just one transcript’s translation (ENSMUST00000120187):
>
> mysql> use mus_musculus_core_75_38;
> mysql> SELECT protein_feature.hit_name, protein_feature.hit_start, protein_feature.hit_end, protein_feature.seq_start, protein_feature.seq_end, protein_feature.evalue, protein_feature.perc_ident, protein_feature.hit_description, protein_feature.translation_id FROM protein_feature, translation WHERE protein_feature.translation_id = '291089’;
>
> But this returns around 1.4 million entries. What might be the best way to get all the protein annotation for ENSMUST00000120187? I’ am aware of BioMart and the Perl API, but would rather query via MySQL. I’ am continuing to read into the InterProScan pipeline and Ensembl documentation, but am not quite sure yet how to go about this.
>
> Thanks for any help!
> Charlie
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info: http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/