I'm converting from Oracle proprietary code to ANSI Standard.
Within one of the older queries within the WHERE clause we had these statements:
Is this ANSI Compliant?
WHERE ee.person_code = p.person_code
AND eo.exam_code = ee.exam_code
AND ed.exam_code = eo.exam_code
AND ee.board_code = eo.board_code
AND eo.board_occurrence = ee.board_occurrence
AND eo.board_code = ed.board_code
I tried the following:
FROM people p
INNER JOIN exam_entries ee on ee.person_code = p.person_code
INNER JOIN exam_occurrences eo on ee.exam_code = eo.exam_code
INNER JOIN exam_details ed on ed.exam_code = eo.exam_code
INNER JOIN exam_entries on ee.board_code = eo.board_code
INNER JOIN exam_occurrences on eo.board_occurrence = ee.board_occurrence
INNER JOIN exam_details on eo.board_code = ed.board_code
The latter query just keeps running and never produces results. Understandable I guess, I'm trying to join tables and then rejoin them.
How would I fix the above result if the first one is not ANSI Compliant?
The use of join conditions in the where clause is ANSI-compliant. Not recommended, but compliant.
The problem with your query is that you are mentioning tables multiple times. And, some of the tables don't have any join conditions, such as the second exam_entries.
Based on your example where clause, you have multiple conditions relating tables. For this, you just want to extend the conditions in the on clause, so I think you want:
FROM people p INNER JOIN
exam_entries ee
on ee.person_code = p.person_code INNER JOIN
exam_occurrences eo
on ee.exam_code = eo.exam_code AND
ee.board_code = eo.board_code AND
ee.board_occurrence = eo.board_occurrence INNER JOIN
exam_details ed
on ed.exam_code = eo.exam_code AND
ed.board_code = eo.board_code