Table Alias Won't Work

[fionama]

Hi,
I have a group of tables that include fact tables, question and answer tables around the delivery of babies. Both the question and the answer lookup tables are relating back to the INCIDENT table via the INCIDENTID field, creating a loop. As I need to gather information on the questions and their answers, I cannot set up a context, and so am trying to use an alias of the ANSWER table. The SQL that I would like to use is as follows :-

Select MAT_INCIDENTS.INCIDENTID,MAT_DELIVERY.ID, MAT_DELIVERY.INCIDENTID,
MAT_QUESTIONNAIREINCIDENTS.QUESTIONNAIREID,
MAT_QUESTIONNAIREINCIDENTS.PAS_NO, MAT_QUESTIONS.ID QUESTIONID,
MAT_QUESTIONS.QUESTIONTEXT,MAT_ANSWERS.ID ANSWERID,MAT_ANSWERS.ANSWER,
alias_MAT_ANSWERS.ID alias_ANSWERID,alias_MAT_ANSWERS.ANSWER aliasANSWER,
BIRTH_DATE
from
MAT_INCIDENTS,
MAT_DELIVERY,
MAT_QUESTIONNAIREINCIDENTS,
MAT_QUESTIONNAIRE,
MAT_QUESTIONS,
MAT_INCIDENTANSWERS,
MAT_ANSWERS,
MAT_ANSWERS alias_MAT_ANSWERS
where MOTHER_PAS_NO='011235'
AND MAT_QUESTIONNAIRE.ID=2
and MAT_QUESTIONS.ID=12345
AND CAST(MAT_DELIVERY.BIRTH_DATE as DATE) BETWEEN '2007-01-01' AND '2007-02-01'
AND MAT_INCIDENTS.INCIDENTID=MAT_DELIVERY.INCIDENTID
and MAT_INCIDENTS.INCIDENTID=MAT_QUESTIONNAIREINCIDENTS.INCIDENTID
and MAT_QUESTIONNAIREINCIDENTS.QUESTIONNAIREID=mat_questionnaire.ID
and MAT_QUESTIONNAIRE.ID=MAT_QUESTIONS.QUESTIONNAIREID
and MAT_QUESTIONS.ID=alias_MAT_ANSWERS.QUESTIONID
and MAT_ANSWERS.ID=mat_incidentanswers.ANSWERID
and MAT_INCIDENTANSWERS.INCIDENTID=MAT_incidents.INCIDENTID

However, this is bringing back multiple rows. When I take out the alias table (alias_MAT_ANSWERS), and just join on the one MAT_ANSWERS table, this brings me back just the one correct row. Unfortunately, this creates the loop, so can't do it in Business Objects. I don't understand what is wrong with the syntax - could anyone help please?

 

[blom0344]

There is probably nothing wrong with the syntax, but you will need to check the cardinalities.
An alias that is added should always have a join between the primary key and a foreign key in a facttable.
(1:n relation ship)
If multiple rows are being returned then relationship may be other than 1:n
In some cases you may need to add additional restriction to the join to enforce 1:n (difficult to judge without having seen the data).
In other cases adding a distinct clause to the the query cures the problem. Not really very sophisticated , but if performance does not suffer, then this may be a quick fix.

Looking at the WHERE clause in the SQL generated I have my doubts about the use of the alias..

Ties Blom