Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Mysql in Perl

Posted by revtopo (revtopo), 2 October 2007
hi there,

I have got to execute two sql queries in a program. In this the result from the first should be used in the second query as a condition.

say $sequence is the result from the first query which has to be used in the second query as a condition. there are about 10 $sequence.

my second sql query is similar to this.

select rna_sequence, hit_string, hit_target, sequence_db from Hsp where rna_sequence  = '$sequence' and sequence_db = '1069'

so, my questions is,  is that correct to use $sequence directly in the place that i have used if notcould you suggest any other method to do.


Posted by admin (Graham Ellis), 2 October 2007
You're correct in principle, I think ... but as you don't give a code example I can't say whether of not you've correctly assigned the data from the first query into that $sequence variable.

Posted by revtopo (revtopo), 2 October 2007
my $sequence;
my $dsn = qq(DBI:mysql:database=$database; host=$host);
$connect = DBI-> connect($dsn, $user, $pass,{printError =>1}) or die $DBI::errstr;
my $query = $connect->prepare(qq(select rna_sequence_sequence, count(rna_sequence_sequence) as count, miRNA  from Cloned_rna where length(Rna_sequence_sequence) = 21 and miRNA is null group by Rna_sequence_sequence order by count desc limit 10  ));
while (my( $sequence, $count, $miRNA) = $query->fetchrow_array()){
$miRNA='N' unless defined $miRNA; # gives the undefined mirna a value since they are null
$sequences{$sequence}= $count;
foreach my $sequence (sort keys %sequences) {
print "$sequence\t$sequences{$sequence}\n";

my $query1 = $connect->prepare(qq(select accession, hit_start, hit_end, Rna_sequence_sequence, hit_string, sequence_idsequence_db from Hsp where rna_sequence_sequence = '$sequence' and Sequence_db_idSequence_db ='1069' limit 10));
while (my ($accession, $hit_start, $hit_end, $Rna_sequence_sequence, $hit_string, $sequence_idsequence_db) = $query1->fetchrow_array()){
print "$accession, $hit_start, $hit_end, $Rna_sequence_sequence, $hit_string, $sequence_idsequence_db\n";

please note that ma using strict in my program

Posted by admin (Graham Ellis), 2 October 2007
You are getting 10 results back and just using the last of the 10 values of $sequence in your second query.  That's fine if it's what you want to do, but I suspect you really wanted to a do a loop there ...

.... which ....

.... deep breath ....

points me to wards suggesting you do the whole thing using a Join in MySQL.

This page is a thread posted to the opentalk forum at and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2015: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: • WEB: • SKYPE: wellho