3dbc has asked for the wisdom of the Perl Monks concerning the following question:

Wise Monks,

I have multiple hashrefs of HoH’s, which are populated with data from multiple tables within a database. Having understood how to associate a Primary Key to a Foreign Key by doing the following: 

$sql1 = “select * from table1”;
$sql2 = “select * from table2”;

my $table1= $dbh->selectall_hashref($sql1, "primary_key");
my $table2= $dbh->selectall_hashref($sql2, "primary_key");

foreach (keys(%$table1)) {
 $table1_foreign_key = 
  $table2->{$table1->{$_}{foreign_key}}{primary_key};
}

[download]

My Problem is this: There exists a $table3 that has a primary_key that is not associated with a foreign_key in $table1. The only way to associate records is through a foreign_key_table3 subkey that exists in both $table1 and $table3 hashrefs. How can I extrapolate records from $table1 and $table3 hashref’s that share the same subkey value for the foreign_key_table3 subkey?

Thanks, 3dbc.

UPDATE: 

foreach $tbl1_primary (keys(%$table1)) {
 foreach (keys(%$table3)) {
  while ( $table3->{$_}{$table1->{$tbl1_primary}{foreign_key}} == 
$table3->{$_}{foreign_key} ) {
  $table1_foreign_key_table3 = 
   $table3->{$_}{$table1->{$tbl1_primary}{foreign_key}};
  }
 }
}

[download]
update2 
$sql_obfuscate = qq {
SELECT *
FROM clinvest.dbf a,
     clinvpmt.dbf b,
     clinvchg.dbf c,
     ernames.dbf d,
     ercontct.dbf e
WHERE a.CID = b.INVESTID
and a.CID = c.INVESTID
and a.NAMEID = d.NAMEID
and a.NAMEID = e.NAMEID
};

[download]

Replies are listed 'Best First'.
Re: associating subkeys of separate HoH's
by Steve_p (Priest) on Apr 09, 2004 at 20:25 UTC

    I'm sorry if I'm misunderstanding something, but why are you using hashes to join with when the database can do the work for you.

    my $sql1 = qq {
SELECT a.*, b.*, c.*
FROM table1 a,
     table2 b,
     table3 c
WHERE a.primary_key = b.foreign_key
and a.foreign_key2 = c.foreign_key2
};

    [download]

    The join syntax may be slightly different depending on your database and the flavor of SQL, but hopefully that should be a lot easier than trying to join by hash.

[reply]
[d/l]
      Unfortunately, I am using DBD::Xbase which does not support table join syntax.
[reply]

        The documentation for DBD::XBase is quite clear on this point:

        Please note that you can only select from one table, joins are not supported and are not planned to be supported. If you need them, get a real RDBMS (or send me a patch).
[reply]
Re: associating subkeys of separate HoH's
by sgifford (Prior) on Apr 09, 2004 at 20:16 UTC

    From your problem description, it seems like you simply want: 

    my $sql3 = "select * from table3";
my $table3 = $dbh->selectall_hashref("foreign_key_table3");
...
foreach (keys(%$table1)) {
  $table1_foreign_key_table3 = 
    $table3->{$table1->{$_}{foreign_key_table3}}{primary_key}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom