List:MySQL and Perl« Previous MessageNext Message »
From:Michael R Boudreau Date:March 30 2011 2:32pm
Subject:Re: MySQL queries in a relational database
View as plain text  
Hi Francesca,

Given the sample tables below, your query should return a single row ('1', '1'). You'll
need to describe in more detail what you mean by "it does not work". In particular, I'd
recommend adding some code to collect error messages, e.g.,

my $sth = $dbh->prepare("...")
    or print "Cannot prepare query: $DBI::errstr\n";

$sth->execute
   or print "Cannot execute query: $DBI::errstr\n";


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'Reilly, 2000.

Paul DuBois, MySQL. 4th edition. Addison-Wesley, 2009.

Paul DuBois, MySQL Cookbook. 2nd edition. O'Reilly, 2007.





On 3/30/11 5:58 AM, "francesca casalino" <francy.casalino@stripped> wrote:

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


--
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


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