8.2 years ago by

France/Nantes/Institut du Thorax - INSERM UMR1087

I'm not an expert on the mysql schema for ensembl, so use my answer with caution; The following SQL query seems to return the data you want:

```
mysql -u anonymous -h ensembldb.ensembl.org -P 5306 -D homo_sapiens_core_61_37f -A
select distinct
V.name,
S.handle,
A.allele,
A.frequency,
M.name,
F.allele_string
from (
allele as A,
variation as V,
subsnp_handle as S,
variation_feature as F
) left join
sample as M
on (M.sample_id = A.sample_id
) where
V.variation_id=A.variation_id and
S.subsnp_id =A.subsnp_id and
F.variation_id=V.variation_id and
V.name="rs3"
order by 1,2,5;
```

results:

```
+------+--------------+--------+-----------+---------------------------------+---------------+
| name | handle | allele | frequency | name | allele_string |
+------+--------------+--------+-----------+---------------------------------+---------------+
| rs3 | 1000GENOMES | C | NULL | NULL | C/T |
| rs3 | 1000GENOMES | T | NULL | NULL | C/T |
| rs3 | 1000GENOMES | C | NULL | 1000GENOMES:CEU | C/T |
| rs3 | 1000GENOMES | T | NULL | 1000GENOMES:CEU | C/T |
| rs3 | 1000GENOMES | C | 0.958333 | 1000GENOMES:low_coverage:CEU | C/T |
| rs3 | 1000GENOMES | T | 0.0416667 | 1000GENOMES:low_coverage:CEU | C/T |
| rs3 | 1000GENOMES | C | 0.958333 | 1000GENOMES:low_coverage:CHBJPT | C/T |
| rs3 | 1000GENOMES | T | 0.0416667 | 1000GENOMES:low_coverage:CHBJPT | C/T |
| rs3 | 1000GENOMES | C | 0.838983 | 1000GENOMES:low_coverage:YRI | C/T |
| rs3 | 1000GENOMES | T | 0.161017 | 1000GENOMES:low_coverage:YRI | C/T |
| rs3 | 1000GENOMES | C | 0.958333 | 1000GENOMES:pilot.1.CEU | C/T |
| rs3 | 1000GENOMES | T | 0.0416667 | 1000GENOMES:pilot.1.CEU | C/T |
| rs3 | 1000GENOMES | C | 0.84 | 1000GENOMES:pilot.1.YRI | C/T |
| rs3 | 1000GENOMES | T | 0.16 | 1000GENOMES:pilot.1.YRI | C/T |
| rs3 | 1000GENOMES | C | NULL | 1000GENOMES:YRI | C/T |
| rs3 | 1000GENOMES | T | NULL | 1000GENOMES:YRI | C/T |
| rs3 | BCM_SSAHASNP | C | NULL | NULL | C/T |
| rs3 | BCM_SSAHASNP | T | NULL | NULL | C/T |
| rs3 | KWOK | C | NULL | NULL | C/T |
| rs3 | KWOK | T | NULL | NULL | C/T |
| rs3 | KWOK | C | 0.967 | CSHL-HAPMAP:HapMap-CEU | C/T |
| rs3 | KWOK | T | 0.033 | CSHL-HAPMAP:HapMap-CEU | C/T |
| rs3 | KWOK | C | 0.956 | CSHL-HAPMAP:HapMap-HCB | C/T |
| rs3 | KWOK | T | 0.044 | CSHL-HAPMAP:HapMap-HCB | C/T |
| rs3 | KWOK | C | 0.978 | CSHL-HAPMAP:HapMap-JPT | C/T |
| rs3 | KWOK | T | 0.022 | CSHL-HAPMAP:HapMap-JPT | C/T |
| rs3 | KWOK | C | 0.89 | CSHL-HAPMAP:HapMap-YRI | C/T |
| rs3 | KWOK | T | 0.11 | CSHL-HAPMAP:HapMap-YRI | C/T |
| rs3 | KWOK | C | 0.95 | KWOK:B | C/T |
| rs3 | KWOK | T | 0.05 | KWOK:B | C/T |
| rs3 | KWOK | C | 0.96 | KWOK:C | C/T |
| rs3 | KWOK | T | 0.04 | KWOK:C | C/T |
| rs3 | KWOK | C | 0.89 | KWOK:H | C/T |
| rs3 | KWOK | T | 0.11 | KWOK:H | C/T |
| rs3 | KWOK | C | 0.93 | KWOK:S | C/T |
| rs3 | KWOK | T | 0.07 | KWOK:S | C/T |
| rs3 | PERLEGEN | C | 0.673913 | PERLEGEN:AFD_AFR_PANEL | C/T |
| rs3 | PERLEGEN | T | 0.326087 | PERLEGEN:AFD_AFR_PANEL | C/T |
| rs3 | PERLEGEN | C | 0.958333 | PERLEGEN:AFD_CHN_PANEL | C/T |
| rs3 | PERLEGEN | T | 0.0416667 | PERLEGEN:AFD_CHN_PANEL | C/T |
| rs3 | PERLEGEN | C | 0.979167 | PERLEGEN:AFD_EUR_PANEL | C/T |
| rs3 | PERLEGEN | T | 0.0208333 | PERLEGEN:AFD_EUR_PANEL | C/T |
| rs3 | SC_SNP | C | NULL | NULL | C/T |
| rs3 | SC_SNP | T | NULL | NULL | C/T |
+------+--------------+--------+-----------+---------------------------------+---------------+
44 rows in set (0.09 sec)
```