Dear Eddie:
Regarding your two tables and the wanted result:
> 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
> +-------------------------------------------------
First remark: that's an unusual table design: I would have expected the
pivoted form of Table2. Why? Well, every time a row is added to Table1 (DML),
a *column* has to be added to Table2 (DDL). Or differently stated: table
content (column "Name" of Table1) and table meta-data (column names of Table2)
are mixed. Moreover, no guarantee can be given by the database that a column
named (say) RAN is present in Table2 whenever a row is inserted into Table1
whose *content* contains "RAN" in column Name. When Table2 would have been
pivoted (i.e., rows and columns interchanged), that guarantee could been given
by the database since then it's a classical referential constraint.
Anyhow, given this design, it's possible to do what you want with DBI:
> Task 1: Select Name from currency_acc where currency = 'SGD'
Your query is fine; but I would extract the full result set in one go, then
close the sth before querying the second table (since you'll need *all* column
names before you can query the second table):
$dbh = DBI->connect($databaseName, $databaseUser, $databasePw)
or die "Connect failed: $DBI::errstr\n";
$sth = $dbh->prepare("SELECT Name FROM currency_acc WHERE currency='SGD'");
$sth->execute();
my @colnames = ();
while (my @names = $sth->fetchrow_array()) { push @colnames, $names[0]; }
> Task 2: Extract amount_type info only for those who have SGD currency
my $query = 'SELECT ACC';
foreach (@colnames) { $query .= ', ' . $_; }
$query .= ' FROM amount_type';
$sth = $dbh->prepare($query);
# At this point, there could be a database error because of a
# non-existing column, since no guarantee can exist about e.g.
# column named "RAN" being present in Table2 when field "RAN"
# appears in column 2 of Table1.
$sth->execute();
while (my @r = $sth->fetchrow_array()) { print "@r\n"; }
(I didn't try this out, so there could be some minor syntax errors -- fixing
those is left as an exercise ;-)
-- Peter Vanroose,
ABIS Training & Consulting
http://www.abis.be/
