I have the following tables:
mysql> describe authors; describe titles; describe titleauthors;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| au_id | varchar(11) | | PRI | | |
| au_lname | varchar(40) | | MUL | | |
| au_fname | varchar(20) | | | | |
| phone | varchar(12) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | char(2) | YES | | NULL | |
| zip | varchar(5) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| title_id | varchar(6) | | PRI | | |
| title | varchar(80) | | MUL | | |
| type | varchar(12) | YES | | NULL | |
| pub_id | varchar(4) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
| advance | decimal(10,2) | YES | | NULL | |
| ytd_sales | int(11) | YES | | NULL | |
| contract | tinyint(1) | | | 0 | |
| notes | varchar(200) | YES | | NULL | |
| pubdate | date | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
10 rows in set (0.01 sec)
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| au_id | char(11) | | PRI | | |
| title_id | char(6) | | PRI | | |
| au_ord | tinyint(4) | YES | | NULL | |
| royaltyshare | decimal(5,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
... authors and titles both have a 1:n relation to titleauthors
And I wrote the following Recordset code to INNER JOIN them all:
require 'dbconn.pl';
use DBIx::Recordset;
use strict;
use vars qw(*set *set2 *set3);
{
my %DEBUG = ('!Debug' => 0);
*set = DBIx::Recordset -> Search
({
conn_dbh(), # returns ('!DataSource' => $dbh )
%DEBUG,
'!Table' => 'authors'
}) ;
while ( my $rec = $set->Next) {
print join "\t", $set{au_fname}, $set{au_lname}, $set{au_id}, $/;
*set2 = DBIx::Recordset -> Search
({
conn_dbh(),
%DEBUG,
'!Table' => 'titleauthors',
au_id => $set{au_id}
}) ;
while ( my $rec2 = $set2->Next) {
# warn 1.3;
print "\t", $set2{title_id}, $/;
# warn 1.4;
*set3 = DBIx::Recordset -> Search
({
conn_dbh(),
%DEBUG,
'!Table' => 'titles',
title_id => $set2{title_id}
});
while ( my $rec3 = $set3->Next) {
print "\t\t", $set3{title}, $/;
}
}
}
}
I know there is a better way using !Link, but I am having problems figuring
out
how to do it. Here is my attempt, but it creates an error.
require 'dbconn.pl';
use DBIx::Recordset;
use strict;
use vars qw(*set *set2 *set3);
my %DEBUG = ('!Debug' => 0);
*set = DBIx::Recordset -> Search
({
conn_dbh(),
%DEBUG,
'!Table' => 'authors',
'!Links' => {
'-titleauthors' => {
'!Table' => 'titleauthors',
'!LinkedField' => 'titleauthors.au_id',
'!MainField' => 'authors.au_id'
}
}
});
while ( my $rec = $set->Next) {
warn $rec->{au_fname};
my $row_count;
while ( my $titleauthors = $set{'-titleauthors'}->Next ) { ### FAILING
LINE
warn $row_count++;
}
}
=head1 ERROR:
Can't call method "Next" without a package or object reference at
3-table-join-link.pl line 30.
DB: Disconnect (id=2, numOpen = 0)
[EMAIL PROTECTED] scripts]$
=cut
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
