foreach $c1(@c1) {
        foreach $c2(@c2)
        {
            $sth = $dbh->prepare( "SELECT a1.id FROM a1, a1_b1, b1  WH
+ERE b1.id = '$c1'AND a1_b1.a1_id=a1.id AND a1_b1.b1_id=b1.id );      
+                                                 
             $sth->execute;
             my $row = $sth->fetchrow(); 
            $sth = $dbh->prepare( "SELECT a1.id  FROM a1, a1_b1, b1 WH
+ERE b1.id = '$c2' AND a1_b1.a1_id=a1.id AND a1_b1.b1_id=b1.id );     
+                                                                     
+              
             $sth->execute;                                           
+       
             my $row1 = $sth->fetchrow( );
            if ($row1 == $row2)
              {
              push @row, $row1;
              }
[download]

Hmm, this is starting to look like more of a sql question than a perl question... one initial suggestion, do you expect more than one match per pair? If not, a LAST could be used to break out of the loop once you find a match, which might save some time.. oh, and your code should say "$row == $row1" IMHO...

Aside from that, a little more detail would help - for instance, where do you get the values for @c1 and @c2 from? Can you guarantee that no value in @c1 is the same as a value in @c2? If not, you are always going to get at least one match - where the two queries return the same row.

The main reason I ask is because if @c1 and @c2 are populated from the results of another query on the same db, you might want to rethink the initial query, or maybe using the key field for the tables to pull all rows where a1.id = a1.id but the keyfield <> the keyfield, and then working on the result-set in pure-perl (depending on the size of the returned data).

Tom Melly, tom@tomandlu.co.uk

thx for your answers. I need all results where $row == $row1, so "LAST" method dont helps. also the @c1 and @c2 arrays comes not from mysql queries but from files , which i parsed before. and of coursed my script checks for the same queries.

No, look again at the answers you've been given above. You want to prepare the SQL statement outside your two foreach blocks, and use bind parameters to substitute the $c1 and $c2 variables on the fly:

use strict;
my $sth = $dbh->prepare("SELECT blah from blah1 where blah1.field = ?"
+);
foreach my $c1(@c1) {
  $sth->execute($c1);
  # do stuff
}
[download]

Your SQL statements are identical, so if (for some reason) you want to spin through two levels of bind parameters, you still only need to prepare the statement once outside the loop.