[ensembl-dev] multi-species MySQL data: how to find out species for a gene?

[Dan Staines]

On 24/09/15 19:01, Dmitry Kuznetsov wrote:
> Hello,
>
> I am trying to analyse bacterial genomes via direct MySQL access to
> **core** databases according to http://ensemblgenomes.org/info/access/mysql
>
> The datasets of my interest (e.g. bacteria_95_collection_core_28_81_1)
> are organised as “collection of species”, though they use the DB schema
> designed initially for single organism datasets. For example,  table
> “genes” contains genes for all 19 species, without any note of species.
> How one could find out genes – species relationship from these
> “multi-species” datasets?

For multispecies databases, you can work back through the seq_region and 
coord_system tables to the meta table which contains genome information
e.g.
select m.meta_value from gene g
join seq_region s using (seq_region_id)
join coord_system c using (coord_system_id)
join meta m using (species_id)
where g.stable_id='ABE92_10660'
and m.meta_key='species.production_name'

(or whichever meta_key is of most use to you)

Hope that helps.

Dan.

-- 
Dan Staines, PhD
Genomics Technology Infrastructure Coordinator
EMBL-EBI, Wellcome Trust Genome Campus
Cambridge CB10 1SD, UK
Tel: +44-(0)1223-492507