8 Join Table... Access to Oracle... Think your good? Try and Master this one!

OK, so I was given this very large query that is trying to create a very large grid table to hold all the necessary information. I really dont like it and suggested breaking it up, but the boss is the boss, and they want to keep it the same so implementation doesn't change :S.  Below I will show the FROM section of the Access query, and the attempt to match that query in the Oracle WHERE clause by another developer.

The problem is that currently they are getting 3000 extra records, so they want me to find the problem.

Access (I tried to structure it nicely):

FROM
(PERSON LEFT JOIN RD_GENDER ON PERSON.GENDER_ID = RD_GENDER.VALUE_ID)
RIGHT JOIN
(((((

     [EMHealth Subject Type] RIGHT JOIN
     (b_collection_pt RIGHT JOIN b_Data_From_Specimn ON
b_collection_pt.SPECIMN_ID = b_Data_From_Specimn.SPECIMN_ID)
      ON
        [EMHealth Subject Type].HSBJ_ID = b_Data_From_Specimn.HSBJ_ID)

LEFT JOIN         b_get_prod_data_Crosstab ON b_Data_From_Specimn.SPECIMN_ID = b_get_prod_data_Crosstab.SPECIMN_ID)

LEFT JOIN HMN_HSBJ ON [EMHealth Subject Type].HSBJ_ID = HMN_HSBJ.HSBJ_ID)

LEFT JOIN ANML_HSBJ ON [EMHealth Subject Type].HSBJ_ID = ANML_HSBJ.HSBJ_ID)

LEFT JOIN RD_GENDER AS RD_GENDER_1 ON ANML_HSBJ.GENDER_ID = RD_GENDER_1.VALUE_ID) ON
PERSON.PERSON_ID = HMN_HSBJ.PERSON_ID.    (I know its ugly......)

Oracle:

WHERE WHERE
  B_DATA_FROM_SPECIMNS.HSBJ_ID = EMHEALTH_SUBJECT_TYPE.HSBJ_ID(+)   AND
  B_DATA_FROM_SPECIMNS.SPECIMN_ID = B_COLLECTION_PT.SPECIMN_ID(+)   AND
  B_DATA_FROM_SPECIMNS.SPECIMN_ID =
B_GET_PROD_DATA_CROSSTAB.SPECIMN_ID(+)
  AND
  PERSON.GENDER_ID = RD_GENDER.VALUE_ID(+)   AND
  ANML_HSBJ.GENDER_ID=RD_GENDER_1.VALUE_ID(+)   AND
  HMN_HSBJ.PERSON_ID=PERSON.PERSON_ID(+)   AND
  HMN_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID   AND
  ANML_HSBJ.HSBJ_ID(+)=EMHEALTH_SUBJECT_TYPE.HSBJ_ID Now some of the Right and Left Joins are mixed up between the Oracle and Access versions, but I think they did this because Oracle doesn't allow two outer joins on the same table in one query.

As I said before, I was thinking unions might be needed. But if anyone has any ideas, or in some miracluous way, an answer.... Post er up!

Thanks for any help you could give,

Mitch Gillespie Received on Thu Oct 19 2006 - 10:34:17 CDT