Hi,
I am trying to pull date from 3 different tables to generate a specifically
formatted text file. I can successfully join two tables using the ANSI LEFT
JOIN, but when I try to add a third this does not work. I have than moved to
using the older perl script " from a,b,c" . This seems to join the data but
the data from the third table is out of order or not placed correctly.
Some background:
Sun Solaris server, SQL 7.5
One database, three tables with following primary fields I am cross
referencing. The first two tables have the same number of rows with primary
keys ExamID and QnAIndex. The third table does not have the same number of
rows and only holds data that has an InstID. Primary keys to match are
ExamID and InstID
Table 1:
ExamQuestionInfo
ExamID
QnAIndex
InstID
Table 2:
ExamID
QnAIndex
Table 3:
Exam ID
InstID
When I run my script if an Exam ID does not exist in Table 3 that row is
not printed. Plus, the InstId is being place on rows that do not have an
instruction. My goal is to print all the rows from Table 1 and Table 2 even
if that do not have an InstID associated with the ExamId. My script
currently is as follows:
#statement handle to query table data
my $sth2 = $dbh->prepare(qq{ SELECT ans.ExamID, ans.QnAIndex, que.QtnText,
que.ExamQnAType, ans.QnAOrder, ans.QnASubOrder, ans.AnsText, ans.CorrectAns
, que.RatSurl, que.DepSurl, que.InstId, inst.InstText FROM ExamQuestionInfo
que, ExamAnswerInfo ans, ExamInstGrpInfo inst WHERE que.ExamID = ans.ExamId
AND que.ExamID = inst.ExamId AND que.QnAIndex = ans.QnAIndex AND que.InstId
= inst.InstId ORDER BY que.ExamID, que.QnAIndex }) or
die ( "Cannot prepare statement: ", $dbh->errstr(), "\n" );
$sth2->execute() or
die ( "Cannot execute statement: ", $sth2->errstr(), "\n" );
#associate Perl variables to output columns
my $rv2 = $sth2->bind_columns(\( $ExamId, $QnAIndex, $QtnText, $ExamQnAType,
$QnAOrder, $QnASubOrder, $AnsText, $CorrectAns, $RatSurl, $DepSurl, $InstId,
$InstText));
Any suggestions, advice very much appreciated.
Michelle
