<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
code
{mso-style-priority:99;
font-family:"Courier New";}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Courier New";}
span.token
{mso-style-name:token;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:818155242;
mso-list-template-ids:-683647624;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p>Also posted to Biostars <a href="https://www.biostars.org/p/200847/">https://www.biostars.org/p/200847/</a>
<o:p></o:p></p>
<p><o:p> </o:p></p>
<p>How would I write an SQL query to retrieve all of the frequencies, form all providers, such as seen on a page like
<a href="http://useast.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=16:89919209-89920209;v=rs1805007;vdb=variation;vf=1232953">
http://useast.ensembl.org/Homo_sapiens/Variation/Population?db=core;r=16:89919209-89920209;v=rs1805007;vdb=variation;vf=1232953</a> ?<o:p></o:p></p>
<p>Connection info: <a href="http://useast.ensembl.org/info/data/mysql.html">http://useast.ensembl.org/info/data/mysql.html</a><o:p></o:p></p>
<p>I'm <em><b>especially</b></em> interested in the 1000 genomes frequencies.<o:p></o:p></p>
<p>I tried this (among many other queries), but it doesn't seem to include the 1000 Genomes Phase 3 data (for example, the frequency for '1000GENOMES:phase_3:TSI', population ID 373537, should be 0.023 )<o:p></o:p></p>
<pre><code>SELECT * FROM allele<o:p></o:p></code></pre>
<pre><code>left </code><span class="token">join</span><code> population<o:p></o:p></code></pre>
<pre><code>on allele.population_id </code><span class="token">=</span><code> population.population_id<o:p></o:p></code></pre>
<pre><code>left </code><span class="token">join</span><code> variation<o:p></o:p></code></pre>
<pre><code>on allele.variation_id </code><span class="token">=</span><code> variation.variation_id<o:p></o:p></code></pre>
<pre><code>where variation.name </code><span class="token">=</span><code> </code><span class="token">'rs1805007'</span><code><o:p></o:p></code></pre>
<ul type="disc">
<li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1">
Do I need to learn about subsnps? <o:p></o:p></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1">
Is the data masked for privacy? There sure are a lot of NULLs.<o:p></o:p></li><li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1">
Do I just need to keep staring at the Ensembl ERD? Like maybe I need to look in some of the sample or individual tables?
<a href="http://useast.ensembl.org/info/docs/api/variation/variation_schema.html">
http://useast.ensembl.org/info/docs/api/variation/variation_schema.html</a> <o:p>
</o:p></li></ul>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Mark Miller<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Instem<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Head of Bioinformatics & SRS Product Manager<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">W +1 610 941 0990 x131<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><i><span style="color:#1F497D">NEW MOBILE NUMBER: +1 215 421 5294</span></i></b><b><i><o:p></o:p></i></b></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal" style="text-align:justify"><span style="font-size:8.0pt;font-family:Arial">The contents of this e-mail message, including any attachments, are intended solely for the use of the person or entity to which the e-mail was addressed. If you
are not the intended recipient of this message, be advised that any dissemination, distribution, or use of the contents of this message is strictly prohibited. If you received this e-mail message in error, please e-mail
<a href=" is@instem.com" ?="">is@instem.com</a> and contact the sender by reply e-mail. Please also permanently delete all copies of the original e-mail and any attached documentation. Thank you. Copyright 2016 Instem Group of Companies. For any other correspondence
please write to Instem plc. a company registered in England and Wales, number 07148099, with registered office at Diamond Way, Stone Business Park, Stone, Staffordshire, ST15 0SD England.</span></p>
</body>
</html>