in reply to Comparing two variables from different loops

OK as requested here is some more information and a small example of the data I am looking to compare with. An example of the output of the first SQL queries I have given is this: 
stop_reference | service_id |     distance     | route_number
----------------+------------+------------------+--------------
 8888888888     |          2 |  137.74251340817 | 2
 1010101010     |          3 | 77.3369252039412 | 3

[download]
Now what I wan to do is search through this table and comparing the service_id here to the service id of the next table. The next table comes from a query where all routes are selected with stops at a certain distance away from our route. What I need to be able to do is to find the matching service id of these two tables and then use a print statement to print the associated information: 
$connectsql = qq{SELECT b.service_id, d.stop_a, d.stop_b FROM bus_stop
+s a, routes b, service c, topology d WHERE a.stop_reference = c.stop_
+reference AND c.service_id = b.service_id AND a.stop_reference = d.st
+op_a ORDER BY a.stop_reference};
$sth = $dbh->prepare( $connectsql );
$sth->execute();
$sth->bind_columns( undef, \$connect_service, \$connect_stop, \$servic
+e_stop );
while( $sth->fetch() ) 
{
print.....
}

service_id |   stop_a   |   stop_b
------------+------------+------------
          3 | 1010101010 | 8888888888
          1 | 3333333333 | 6666666666
          2 | 6666666666 | 3333333333
          2 | 8888888888 | 1010101010

[download]
Now while I can find if the last two service id's on each table match i need to be able to loop through them all and compare every row produced by the queries. Note the number of rows will be much larger this is just an example to work with for simplicity.

Replies are listed 'Best First'.
Re^2: Comparing two variables from different loops
by Eimi Metamorphoumai (Deacon) on Jul 05, 2007 at 14:44 UTC
    Many others have suggested hashes, and that's definitely a valid approach. I'm curious, though, whether you couldn't achieve the same thing in SQL. That is, maybe you should just create a single query JOINing the two queries on service_id. That might reduce the amount of data you need to pull into Perl, and let the SQL server do the heavy lifting for you (which is what it's good at, after all).
[reply]
[d/l]
[select]

      Please try the SQL given below. This SQL is NOT tested, but should return records with origin_service == destination_service. Please refer to Subqueries with ANY, IN, and SOME

      SELECT 
    a.stop_reference, 
    b.service_id, 
    distance(PointFromText('POINT($origin)', 27700),east_north), 
    c.route_number 
FROM 
    bus_stops a, 
    service b, 
    routes c 
WHERE 
    distance(PointFromText('POINT($origin)', 27700),east_north) < 200 
    AND 
        a.stop_reference = b.stop_reference 
    AND 
        b.service_id = c.service_id 
    AND
        b.service_id IN (
        SELECT 
            DISTINCT e.service_id 
        FROM 
            bus_stops d, 
            service e, 
            routes f 
        WHERE 
            distance(PointFromText('POINT($dest)', 27700),east_north) 
+< 200 
            AND 
            d.stop_reference = e.stop_reference 
            AND 
            e.service_id = f.service_id 
        ORDER BY 
            e.service_id, 
            distance(PointFromText('POINT($dest)', 27700),east_north) 
    )
ORDER BY 
    b.service_id, 
    distance(PointFromText('POINT($origin)', 27700),east_north)

      [download]

      --VC

[reply]
[d/l]

In Section Seekers of Perl Wisdom