List:MySQL and Perl« Previous MessageNext Message »
From:francesca casalino Date:March 30 2011 10:58am
Subject:MySQL queries in a relational database
View as plain text  
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…if 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=INNODB
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 = 0
and 0 for each variation and store them in one group, and the ones that have
allele.reference = 0 and 1 in another group, and the ones that have
allele.reference = 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 = genotype.allele_id

group by allele.variation_id;



#Count the number of samples that have reference=0 for each variation:

select allele.variation_id, count(genotype.allele_id)

from allele, genotype

where allele.allele_id = genotype.allele_id and allele.reference=0

group by allele.variation_id;



When I try this in Perl it does not work…

#count number of alleles entered for each variation:

my $sth =$dbh->prepare("SELECT allele.variation_id,
count(genotype.allele_id)

from allele, genotype

where allele.allele_id = genotype.allele_id

group by allele.variation_id");

$sth->execute();

my (@allele);

while (@allele = $sth->fetchrow_array()) {

print @allele."\n";

}



2) I really have no idea how to record the values of the successive entries
of “reference” grouped by variation and sample_id… I have been looking
through MySQL tutorials, but I really need to understand this better to know
how to approach this…



#Count the number of samples that have reference =0 for both entries for
each variation

select allele.variation_id, count(genotype.sample_id)

from allele JOIN genotype ON allele.allele_id = 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…



-francesca

Thread
MySQL queries in a relational databasefrancesca casalino30 Mar
  • Re: MySQL queries in a relational databaseMichael R Boudreau30 Mar
    • Re: MySQL queries in a relational databasefrancesca casalino30 Mar
  • Re: MySQL queries in a relational databaseDarren Duncan30 Mar
    • Re: MySQL queries in a relational databasefrancesca casalino31 Mar
      • Re: MySQL queries in a relational databaseDarren Duncan31 Mar
        • Re: MySQL queries in a relational databasefrancesca casalino31 Mar