Re: MySQL queries in a relational database

--bcaec519643be00144049fb48d96
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Hi and thank you for your replies and suggestions.

I realise I was not clear at all, sorry!

The problem I have with both of my questions is actually that I don't reall=
y
know how to do it and whether I am approaching the problems correctly (well=
,
just the first problem, for the second I don't even have ideas...)

I can get the counts of the alleles for each variation, and the counts of
the minor allele, but I do not know how to store and retrieve the values fo=
r
both of these select statements, in order to calculate the frequency (for
each variation_id) and insert this frequency in another MySQL table...

Here is what I can do so far:

#count number of alleles entered for each variation: This gives me 2
columns: one with the variation_id and one with the count
my $sth =3D$dbh->prepare("SELECT allele.variation_id,
count(genotype.allele_id)
from allele, genotype
where allele.allele_id =3D genotype.allele_id
group by allele.variation_id");
$sth->execute();
my (@allele_count);
while (@allele_count =3D $sth->fetchrow_array()) {
print join (",", @allele_count), "\n";
}
$sth->finish;

#Count the number of samples that have alternate (minor) allele: This gives
me two columns: one with the variation_id and one with the minor allele
counts

my $sth =3D$dbh->prepare("SELECT allele.variation_id,
count(genotype.allele_id)
from allele, genotype where allele.allele_id =3D genotype.allele_id and
allele.reference=3D1

group by allele.variation_id");
$sth->execute();
my (@min_allele_count);
while (@min_allele_count =3D $sth->fetchrow_array()) {
print join (",", @min_allele_count), "\n";
}
$sth->finish;


I then need to divide min_allele_count by allele_count to get the frequency=
,
but as it is I cannot get these values because they are not stored...I hope
this helps explaining 1) ....

Thank you for any suggestions..
-francy


2011/3/30 Michael R Boudreau <[email protected]>

>  Hi Francesca,
>
> Given the sample tables below, your query should return a single row (=91=
1=92,
> =911=92). You=92ll need to describe in more detail what you mean by =93it=
 does not
> work=94. In particular, I=92d recommend adding some code to collect error
> messages, e.g.,
>
> my $sth =3D $dbh->prepare(=93...=94)
>     or print =93Cannot prepare query: $DBI::errstr\n=94;
>
> $sth->execute
>    or print =93Cannot execute query: $DBI::errstr\n=94;
>
>
> Here are some books that I have found helpful for learning MySQL and the
> Perl DBI:
>
> Alligator Descartes and Tim Bunce, *Programming the Perl DBI*. O=92Reilly=
,
> 2000.
>
> Paul DuBois, *MySQL*. 4th edition. Addison-Wesley, 2009.
>
> Paul DuBois, *MySQL Cookbook*. 2nd edition. O=92Reilly, 2007.
>
>
>
>
>
>
> On 3/30/11 5:58 AM, "francesca casalino" <[email protected]>
> wrote:
>
> Dear Perl and MySQL gurus,
>
>
>
> I have been learning a lot from you for the past month, and, learning fro=
m
> your comments and with your help, I was able to build a database on MySQL
> through Perl DBI.
>
>
>
> I am now having trouble with generating a query in Perl DBI to take data
> from this database and insert it into another table in MySQL; I am still
> just at the start in learning both MySQL and Perl, and sorry if this is a
> simple question again, but I have been stuck on this for a while now=85if=
 you
> have any advice on how to tackle this please let me know.
>
>
>
> My database is constructed with different tables connected to each other
> through foreign keys. This is an example of the data that I am having
> trouble with (primary and foreign keys are specified in parenthesis, and
> foreign keys refer to another table in the database). I used ENGINE=3DINN=
ODB
> for constructing the database, and MySQL version 5.1.
>
>
>
>
>
> *Allele*
>
> Allele_id (Primary key)            Variation_id (Foreign key)        Alle=
le
>             Reference
>
> 12                                            1
> A                     0
>
> 13                                            1
> G                     1
>
>
>
> *Genotype*
>
> Genotype_id (Primary key)       Sample_id (Foreign key)           Allele_=
id
> (Foreign key)
>
> 1                                              Sample1
>             12
>
> 2                                              Sample1
> 13
>
>
>
>
>
>
>
> I am trying to:
>
> 1) Fill in another table which contains frequencies for each of these
> alleles, for each variation, and
>
> 2) Find a way to select and count the samples that have allele.reference =
=3D
> 0
> and 0 for each variation and store them in one group, and the ones that
> have
> allele.reference =3D 0 and 1 in another group, and the ones that have
> allele.reference =3D 1 and 1 ina athird group.
>
>
>
> For 1) I have solved the counts that I will need for the frequency
> calculations on MySQL, but for some reason it does not work when I try th=
e
> first of these queries on Perl. The Mysql is this:
>
>
>
> #count number of alleles entered for each variation:
>
> select allele.variation_id, count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id =3D genotype.allele_id
>
> group by allele.variation_id;
>
>
>
> #Count the number of samples that have reference=3D0 for each variation:
>
> select allele.variation_id, count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id =3D genotype.allele_id and allele.reference=3D0
>
> group by allele.variation_id;
>
>
>
> When I try this in Perl it does not work=85
>
> #count number of alleles entered for each variation:
>
> my $sth =3D$dbh->prepare("SELECT allele.variation_id,
> count(genotype.allele_id)
>
> from allele, genotype
>
> where allele.allele_id =3D genotype.allele_id
>
> group by allele.variation_id");
>
> $sth->execute();
>
> my (@allele);
>
> while (@allele =3D $sth->fetchrow_array()) {
>
> print @allele."\n";
>
> }
>
>
>
> 2) I really have no idea how to record the values of the successive entri=
es
> of =93reference=94 grouped by variation and sample_id=85 I have been look=
ing
> through MySQL tutorials, but I really need to understand this better to
> know
> how to approach this=85
>
>
>
> #Count the number of samples that have reference =3D0 for both entries fo=
r
> each variation
>
> select allele.variation_id, count(genotype.sample_id)
>
> from allele JOIN genotype ON allele.allele_id =3D genotype.allele_id
>
> group by allele.variation_id, genotype.sample_id;
>
>
>
>
>
> Thank you VERY VERY much for any help/suggestions, or any books/tutorials
> that I could look at to understand how to solve these problems=85
>
>
>
> -francesca
>
>
> --
> Michael R. Boudreau
> Senior Publishing Technology Analyst
> The University of Chicago Press
> 1427 E. 60th Street
> Chicago, IL 60637
> (773) 753-3298  fax: (773) 753-3383
>
>

--bcaec519643be00144049fb48d96--