MySQL queries in a relational database

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

Dear Perl and MySQL gurus,



I have been learning a lot from you for the past month, and, learning from
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 y=
ou
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=3DINNOD=
B
for constructing the database, and MySQL version 5.1.





*Allele*

Allele_id (Primary key)            Variation_id (Foreign key)        Allele
            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 hav=
e
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 the
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 entries
of =93reference=94 grouped by variation and sample_id=85 I have been lookin=
g
through MySQL tutorials, but I really need to understand this better to kno=
w
how to approach this=85



#Count the number of samples that have reference =3D0 for both entries for
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

--20cf304346f43b2a7f049fb11085--