Extracting values from one table and feed the values as a query for second table

[eddieson]

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

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

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

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

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();

Thanks for your help Eddie

[Anonymous Monk]

Build your second query exactly as you would manually

my $col_clause = join(', ', @names);
my $sql  = "SELECT ".$col_clause." FROM ...";
my $sth2 = $dbh->prepare($sql) ...

This assumes your column names can only be simple identifiers (no spaces, quotes, commas etc). Incidentally, your table design is almost certainly going to cause you trouble later on. That's a topic for a different forum though.

[eddieson]

Thanks loads! It works fine now. Yes I completely agree that the tables are troublesome here, but I don't have permission to change. my task is just to display. Thanks again. <\p>

[sundialsvc4]

Naturally the question arises in my mind, “is it possible to use a JOIN here?”   In my experience, repetitive queries are costly even when you have prepared the query once and keep re-using the same statement handle.   I really like to push the chore of finding matching records onto the database engine when I can.   I have not examined your code closely.

[TomDLux]

There's something backwards about table 2. The number of records is constant, expect when a new type of account is encountered, but the database schema needs to change every time you add or drop a person. Should be the other way around.