Get A File To Cross Reference Ensembl Gene Ids And Alias

Question

[D W]

I have a list of Ensembl gene IDs. I would like a file to cross reference them to aliases from kgAlias.

Preferably it would be some kind of mysql statements with a join of of some kind:

mysql -N -h  genome-mysql.cse.ucsc.edu -A -u genome -D hg18 -e 'select name,name2,exonStarts,exonEnds from ensGene'

UPDATE: Pierre answered this question:

mysql -N -h  genome-mysql.cse.ucsc.edu -A -u genome -D hg18 -e '\
 select QUERY.name,QUERY.name2,QUERY.geneSymbol from
 (select X.*,\
 G.* from ensGene as G,\
 knownToEnsembl as KE,\
 kgXref as X where\
 G.name=KE.value and KE.name=X.kgID)\
 as QUERY'

Answer 1

[Pierre Lindenbaum]

From the table Browser ("describe table schema"), you get:

For ensGene:

hg19.knownToEnsembl.value (via ensGene.name)

From knownToEnsembl

hg19.kgXref.kgID (via knownToEnsembl.name)

All in one, your request would be:

[D W]

Thanks very much. May I ask, how can I restrict the results to output only the name (ex.ENST00000250784), name2 (ex. ENSG00000129824), and geneSymbol (ex. RPS4Y1)?

[Pierre Lindenbaum]

add ... ' and G.name="ENST00000250784" and X.geneSymbol="RPS4Y1" ' at the end of the query. See. http://dev.mysql.com/doc/refman/5.0/en/select.html

[D W]

thanks for the link

Answer 2

[D W]

Pierre's solution was what I was looking for. But just for completeness you can get this from the web as well.

• GOTO: http://www.biomart.org/biomart/martview/
• Select database: Ensembl Genes Select
• dataset: Homo Sapiens Click
• Attributes Exapand: External
• References Check: HGNC automatic gene
• name Click Top Button: Results
• Select output desired

http://www.biomart.org/biomart/martview/6a219783b4cc3e15820e9ea9f654e555