map attributes between three columns and extract common

[haojam]

Hi,

I am running a perl script to retrieve common values in three columns at mysql tables. I would like to map three columns for finding same $chr, $start, $end or values within $start and $end if exists. I have attached the text files for two tables earlier. I would be glad to see your positive response if You could please help me.

Regards,
Rocky
Seoul National University

#!/usr/bin/perl -w
use strict;
use DBI;
my $user = 'root';
my $password = '1004';

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mirvar", $user, $password) || die "Failed connect DB : $!\n";

my $sql;
my $sql1;
#my $sql2;

$sql = "select * from dbsnp129 limit 10";
$sql1 = "select * from mirna limit 10";

# prepare the query

my $sth = $dbh->prepare($sql);
my $sth1 = $dbh->prepare($sql1);


# execute the query with parameter
$sth->execute || die "Error! : $sql\n";
$sth1->execute || die "Error! : $sql1\n";

while ( my $line1 = $sth->fetchrow_hashref() ) {
        my $chr = $line1->{'chrom'};
        my $start = $line1->{'chromStart'};
        my $end = $line1->{'chromEnd'};
        my $strand = $line1->{'strand'};
        print "$chr\t$start\t$end\t$strand\n";
        }
        while ( my $line2 = $sth1->fetchrow_hashref() ) {
                my $chr = $line2->{'chrom'};
                my $start = $line2->{'chromStart'};
                my $end = $line2->{'chromEnd'};
                my $strand = $line2->{'strand'};
                print "$chr\t$start\t$end\t$strand\n";
        }

                if ( $line2->{'chrom'} == $line1->{'chrom'} && ($line2->{'chromStart'} <= $line1->{'chromStart'} && $line1->{'chromEnd'} <= $line2->{'chromEnd'} )) {

                        print "$chr\t$start\t$end\t$start\t$end\n";
        }