Re: MySQL queries in a relational database

--001636e0a87bba1ca6049fc3f4c2
Content-Type: text/plain; charset=ISO-8859-1

Hi Duncan,

Thank you again for the very useful advice! I am not sure how much better it
is to have the insert stetment like this and I think I am forced to use
placeholders in this case, since I realised that I also need the
last_insert_id for the population and for the variation_id that are foreign
keys in the frequency table, so the new error that comes up is "Cannot add
or update a child row: a foreign key constraint fails"...

But your join structure is much more clean so I will definitly use that.
What I am trying to do is to calculate a frequency with the two counts I get
from two insert statements, and for now I am getting the two counts
seperately, but I do not know how to go about to calculate the division
between these counts in the most convenient way: should I try to do it
directly in the same statement, or maybe I can insert these columns in the
"frequency" table and then retrieve them and devide them? I really do not
kow how to go about doing this so if anybody has an idea could you please
let me know?

I tried dividing the two sleect statements (giving counts), but this is the
error message I get:
"Argument "SELECT allele.variation_id, count(genotype.allele_id) as..."
isn't numeric in division (/) at maf.pl line 16.
Illegal division by zero at maf.pl line 16"

I was also trying to isert the two statements into one using SELECT (SELECT
but I get the error message "DBD::mysql::st execute failed: Subquery returns
more than 1 row at freq.pl line 26." And I don't think this would solve my
problem anyway...Just trying things but I can't seem to get to the answer...

Thank you for any suggestions...
-f
2011/3/31 Darren Duncan <[email protected]>

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

--001636e0a87bba1ca6049fc3f4c2--