<div dir="ltr">Hello,<div><br></div><div>Your query is almost there; the issue is that you are restricting the results by coordinates to the boundaries of the gene.</div><div><br></div><div>The consequence table that you are referring to includes variants classified as "Upstream gene variant" and "Downstream gene variant" - these are variants that fall within 5kb of the transcript boundaries.</div>
<div><br></div><div>If you drop the following lines from your statement you should get the correct result:</div><div><br></div><div><p class="" style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif">
<b>vf.seq_region_id=27513 and<u></u><u></u></b></p><p class="" style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif"><b>vf.seq_region_start>=32889611 and<u></u><u></u></b></p><p class="" style="margin:0in 0in 0.0001pt;font-size:11pt;font-family:Calibri,sans-serif">
<b>vf.seq_region_end<=32973805 and</b></p><br>One further source of discrepancy may be failed variants - by default we do not show variants that have been flagged as failed. Depending on whether you want to include these, you may want to left join to failed_variation via vf.variation_id to check the failed status.</div>
<div><br></div><div>Hope this helps!<br><br></div><div>Will McLaren</div><div>Ensembl Variation</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 29 August 2013 05:38, Jayaraman, Pushkala <span dir="ltr"><<a href="mailto:pjayaraman@mcw.edu" target="_blank">pjayaraman@mcw.edu</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div>
<p class="MsoNormal">Hello, <u></u><u></u></p>
<p class="MsoNormal">Im currently a developer at the Rat Genome database, Human and Molecular Genetics Center, MCW. I’ve currently been assigned a project wherein the PIs are referencing Ensembl gene pages and gene sequence and variant information. The first
step in my application pipeline is to get the sequence for the gene of interest and all the variation consequences within that genic region.
<u></u><u></u></p>
<p class="MsoNormal">For eg. Here:<u></u><u></u></p>
<p class="MsoNormal"><a href="http://www.ensembl.org/Homo_sapiens/Gene/Variation_Gene/Table?g=ENSG00000139618;r=13:32889611-32973805#ALL_tablePanel" target="_blank">http://www.ensembl.org/Homo_sapiens/Gene/Variation_Gene/Table?g=ENSG00000139618;r=13:32889611-32973805#ALL_tablePanel</a><u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">now I have access to your MYSQL database for homo_sapiens_variation_72_37 and also have a database dump ( since we thought creating a local copy would make more sense) . im using the <a href="http://useastdb.ensembl.org" target="_blank">useastdb.ensembl.org</a> port 5306.
<u></u><u></u></p>
<p class="MsoNormal">The problem arises when I try and simulate a query from the database that gives me the variants exactly like they are on the gene report page above.
<u></u><u></u></p>
<p class="MsoNormal">For the same gene using the homo_sapiens_variation_72_37 schema, I have the following test query:<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><b>select vf.variation_name, vf.seq_region_id, vf.seq_region_start, vf.source_id, <a href="http://s.name" target="_blank">s.name</a>, vf.minor_allele_freq, tv.feature_stable_id, tv.allele_string, tv.consequence_types
<u></u><u></u></b></p>
<p class="MsoNormal"><b>from homo_sapiens_variation_72_37.variation_feature vf, homo_sapiens_variation_72_37.transcript_variation tv,
<u></u><u></u></b></p>
<p class="MsoNormal"><b>homo_sapiens_variation_72_37.source s<u></u><u></u></b></p>
<p class="MsoNormal"><b>WHERE <u></u><u></u></b></p>
<p class="MsoNormal"><b>s.source_id=vf.source_id and<u></u><u></u></b></p>
<p class="MsoNormal"><b>vf.seq_region_id=27513 and <u></u><u></u></b></p>
<p class="MsoNormal"><b>vf.seq_region_start>=32889611 and <u></u><u></u></b></p>
<p class="MsoNormal"><b>vf.seq_region_end<=32973805 and<u></u><u></u></b></p>
<p class="MsoNormal"><b>vf.variation_feature_id=tv.variation_feature_id and <u></u>
<u></u></b></p>
<p class="MsoNormal"><b>tv.feature_stable_id in (<u></u><u></u></b></p>
<p class="MsoNormal"><b>select t.stable_id from homo_sapiens_core_72_37.transcript t<u></u><u></u></b></p>
<p class="MsoNormal"><b>where t.gene_id=609208<u></u><u></u></b></p>
<p class="MsoNormal"><b>)<u></u><u></u></b></p>
<p class="MsoNormal"><b>order by tv.feature_stable_id;<u></u><u></u></b></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">where seq_region_id is Chr 13 and region start corresponds to the start of the variant.
<u></u><u></u></p>
<p class="MsoNormal">Even then my count of the data is only 13270 while the web page gives me their count as 13584.
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">The thing is that I need to get in touch with any developer at Ensembl who knows this stuff well and can point me in the direction creating the correct query to get the same number of variation consequences as the webpage.
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Please do let me know if you can help me with this, or if you know anyone who can help me out with this..
<u></u><u></u></p>
<p class="MsoNormal">Since this is just the first stage of the project, im looking for a bunch of solid examples wherein my query and the results on the webpage are spot-on correct.
<u></u><u></u></p>
<p class="MsoNormal">Hope you guys have a good rest of your summer!<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting"">Pushkala Jayaraman<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting"">Programmer/Analyst - Rat Genome Database<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting"">Human and Molecular Genetics Center<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting"">Medical College of Wisconsin<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting""><a href="tel:414-955-2229" value="+14149552229" target="_blank">414-955-2229</a><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:16.0pt;font-family:"Arabic Typesetting""><a href="http://rgd.mcw.edu" target="_blank"><span style="color:blue">http://rgd.mcw.edu</span></a><u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>_______________________________________________<br>
Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>
Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>
Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br>
<br></blockquote></div><br></div>