<html><head><meta http-equiv="Content-Type" content="text/html charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi all,<div>as in the object, I'm trying to get gene names from a list of Ensembl IDs.</div><div>I would like to do this programmatically, by directly connecting and querying the Mysql db by the following query:</div><div><br></div><div><pre class="prettyprint" style="padding: 2px; font-family: Menlo, Monaco, 'Courier New', monospace; font-size: 12px; color: rgb(51, 51, 51); border-top-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; border-bottom-left-radius: 4px; margin-top: 0px; margin-bottom: 18px; line-height: 18px; background-color: rgb(245, 245, 245); border: 1px solid rgb(136, 136, 136); white-space: pre-wrap; word-break: break-all; position: static; z-index: auto; "><code style="padding: 0px; font-family: Menlo, Monaco, 'Courier New', monospace; color: rgb(221, 17, 68); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px solid white; "><span class="pln" style="color: rgb(0, 0, 0); ">mysql </span><span class="pun" style="color: rgb(102, 102, 0); ">-</span><span class="pln" style="color: rgb(0, 0, 0); ">h ensembldb</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">ensembl</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">org </span><span class="pun" style="color: rgb(102, 102, 0); ">--</span><span class="pln" style="color: rgb(0, 0, 0); ">port </span><span class="lit" style="color: rgb(0, 102, 102); ">5306</span><span class="pln" style="color: rgb(0, 0, 0); "> </span><span class="pun" style="color: rgb(102, 102, 0); ">-</span><span class="pln" style="color: rgb(0, 0, 0); ">u anonymous </span><span class="pun" style="color: rgb(102, 102, 0); ">-</span><span class="pln" style="color: rgb(0, 0, 0); ">D homo_sapiens_core_47_36i </span><span class="pun" style="color: rgb(102, 102, 0); ">-</span><span class="pln" style="color: rgb(0, 0, 0); ">A
</span><span class="pun" style="color: rgb(102, 102, 0); ">></span><span class="pln" style="color: rgb(0, 0, 0); "> </span><span class="kwd" style="color: rgb(0, 0, 136); ">select</span><span class="pln" style="color: rgb(0, 0, 0); "> distinct
G</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">stable_id</span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); ">
S</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">synonym
</span><span class="kwd" style="color: rgb(0, 0, 136); ">from</span><span class="pln" style="color: rgb(0, 0, 0); ">
gene_stable_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">as</span><span class="pln" style="color: rgb(0, 0, 0); "> G</span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); ">
object_xref </span><span class="kwd" style="color: rgb(0, 0, 136); ">as</span><span class="pln" style="color: rgb(0, 0, 0); "> OX</span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); ">
external_synonym </span><span class="kwd" style="color: rgb(0, 0, 136); ">as</span><span class="pln" style="color: rgb(0, 0, 0); "> S</span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); ">
xref </span><span class="kwd" style="color: rgb(0, 0, 136); ">as</span><span class="pln" style="color: rgb(0, 0, 0); "> X </span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); ">
external_db </span><span class="kwd" style="color: rgb(0, 0, 136); ">as</span><span class="pln" style="color: rgb(0, 0, 0); "> D
</span><span class="kwd" style="color: rgb(0, 0, 136); ">where</span><span class="pln" style="color: rgb(0, 0, 0); ">
D</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">external_db_id</span><span class="pun" style="color: rgb(102, 102, 0); ">=</span><span class="pln" style="color: rgb(0, 0, 0); ">X</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">external_db_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">and</span><span class="pln" style="color: rgb(0, 0, 0); ">
X</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">xref_id</span><span class="pun" style="color: rgb(102, 102, 0); ">=</span><span class="pln" style="color: rgb(0, 0, 0); ">S</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">xref_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">and</span><span class="pln" style="color: rgb(0, 0, 0); ">
OX</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">xref_id</span><span class="pun" style="color: rgb(102, 102, 0); ">=</span><span class="pln" style="color: rgb(0, 0, 0); ">X</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">xref_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">and</span><span class="pln" style="color: rgb(0, 0, 0); ">
OX</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">ensembl_object_type</span><span class="pun" style="color: rgb(102, 102, 0); ">=</span><span class="str" style="color: rgb(0, 136, 0); ">"Gene"</span><span class="pln" style="color: rgb(0, 0, 0); "> </span><span class="kwd" style="color: rgb(0, 0, 136); ">and</span><span class="pln" style="color: rgb(0, 0, 0); ">
G</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">gene_id</span><span class="pun" style="color: rgb(102, 102, 0); ">=</span><span class="pln" style="color: rgb(0, 0, 0); ">OX</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">ensembl_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">and</span><span class="pln" style="color: rgb(0, 0, 0); ">
G</span><span class="pun" style="color: rgb(102, 102, 0); ">.</span><span class="pln" style="color: rgb(0, 0, 0); ">stable_id </span><span class="kwd" style="color: rgb(0, 0, 136); ">in</span><span class="pln" style="color: rgb(0, 0, 0); "> </span><span class="pun" style="color: rgb(102, 102, 0); ">(</span><span class="str" style="color: rgb(0, 136, 0); ">"ENSG00000197021"</span><span class="pun" style="color: rgb(102, 102, 0); ">,</span><span class="pln" style="color: rgb(0, 0, 0); "> </span><span class="str" style="color: rgb(0, 136, 0); ">"ENSG00000204379"</span><span class="pun" style="color: rgb(102, 102, 0); ">);</span></code></pre><div>This code generally works, but I do not figure out why I do not get any result for some ENS ids.</div></div><div><br></div><div>For example, this ID: <a href="http://www.ensembl.org/Homo_sapiens/Gene/Summary?g=ENSG00000130208" style="color: rgb(51, 102, 153); font-size: 1.25em; font-family: 'Luxi Sans', Helvetica, Arial, Geneva, sans-serif; line-height: 16px; background-color: rgb(255, 255, 255); ">ENSG00000130208</a> is associated to the APOC1 gene.</div><div>If I use this ID in the query, I did not get any result. in fact, the <b>external_synonymous</b> table does contain the name "APOC1".</div><div><br></div><div>However and obviously, by the Ensembl web interface I can get this information.</div><div><br></div><div>Where am I going wrong? Should I use other and more proper tables?</div><div><br></div><div>Thanks</div><div>Tommaso</div></body></html>