Re: MySQL queries in a relational database

francesca casalino wrote:
> Hi Duncan,
> Thank you for the hint about doing the insert and select in one step. I
> tried that but it is not inserting any records, and it does not give me an
> error message.
> This is what I am doing:
> 
> #count number of alleles entered for each variation:
> 
> my $sth =$dbh->prepare("INSERT into frequency (total_count)
> SELECT count(genotype.allele_id)
> from allele, genotype
> where allele.allele_id = genotype.allele_id
> group by allele.variation_id");
> $sth->execute();
> $sth->finish;

The first thing to try when that happens is to run the SELECT portion by itself 
and see if it returns any rows.  If it doesn't, then the above INSERT...SELECT 
would be a no-op.

The only thing that would cause no rows for the above query is if the two tables 
"allele" and "genotype" have no rows matching each other.

Although you have a count(), the fact you also have a group-by means that the 
query could return zero rows.

Another thing you should do when writing SQL is to put the table join conditions 
in the FROM clause rather than the WHERE clause, like this:

   INSERT into frequency (total_count)
   SELECT count(genotype.allele_id)
     from allele inner join genotype on allele.allele_id = genotype.allele_id
     group by allele.variation_id;

Moreover, because you are joining on common column names, this shorter version 
is better:

   INSERT into frequency (total_count)
   SELECT count(allele_id)
     from allele inner join genotype using (allele_id)
     group by allele.variation_id;

When you say "using", besides being shorter, the two matching columns are also 
combined into one automatically, so you can just say "allele_id" elsewhere and 
it won't be confused about "which table" since it doesn't matter.  (The only 
time it might matter is if you're doing an outer join and you want to explicitly 
test that a row didn't match by testing that the other column copy is null.)

-- Darren Duncan

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]