Dear members

I have two tables: currency_acc and amount_type. I would like to extract Names from the first table who have SGD currency and keep as a query for the second table 


Table 1 currency_acc

+---------------------------------------
ID    Name    currency        +
+---------------------------------------
23    RAM    SGD
32    GOP    GBP
45    RAN    SGD
67    KRI      USD
78    YUR    SGD
+------------------------------------

Table 2 amount_type

+-------------------------------------------------
ACC    RAN    KRI    YUR    RAM  +
+------------------------------------------------
SAV    675    890    760    234
CUR    345    123    567    120

NRE    123    256    897    145

NRO    787    978    545    678

+-------------------------------------------------

[download]

Task 1: Select Name from currency_acc where currency = 'SGD' Task 2: Extract the amount_type information only for those guys who have SGD currency 

Ideal output:

+-----------------------------------------
ACC    RAN    YUR    RAM  +
+-----------------------------------------
SAV    675    760    234
CUR    345    567    120

NRE    123    897    145

NRO    787    545    678

+-----------------------------------------

[download]

I tried the following perl script, which didn't return the ideal output. 


$dbh = DBI->connect($databaseName, $databaseUser, $databasePw) || die
"Connect failed: $DBI::errstr ";

$sth = $dbh->prepare("SELECT Name FROM currency_acc WHERE
currency='SGD'"); #select the name from first table1
$sth->execute();
my $i=0;
$count=0;

while (@names = $sth->fetchrow_array()) {
     $count += ($names[0] =~ /^[A-Z]/i);     #Counting howmany names
need to be queried from the 2nd table

       for ($i=0; $i<$count; $i++) {
       $sth2 = $dbh->prepare("SELECT ACC, $names[$i], $names[$i++]
FROM amount_type"); # Keeping the names as header
       $sth2->execute();

               while(@new_result = $sth2->fetchrow_array()) {
               push @new_result2, $new_result[0];

               }

       }

}
print "@new_result2 \n";
$sth->finish();
$sth2->finish();

[download]

Thanks for your help Eddie

In reply to Extracting values from one table and feed the values as a query for second table by eddieson

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.