ORA-00904 when using OUTER JOIN with 3 tables in clause

Question

SELECT 
   s.session_name semester
FROM 
   lsx.elsp_session s,
   lsx.elsp_course_offering co,
   its.ACCOUNT ac
LEFT OUTER JOIN lsx.elsp_class_attendance ca on ca.course_id = CO.ID and ca.student_number = ac.id
WHERE co.session_id = s.session_id

I am getting an ORA-00904 error and the problem seems to be with "CO.ID" being an invalid identifier. However, if I put the "lsx.elsp_course_offering co" table to be last in the FROM list, then "ac.id" becomes the problem identifier.

Is it not possible to use another table in a JOIN clause? I seem to recall creating successful queries like this using MySQL.

I am using Oracle 9.2.0.8.0.

Following the advice I received, I was able to get the join working as expected when I reworked the query to this:

SELECT s.session_name semester FROM lsx.elsp_session s
  INNER JOIN lsx.elsp_course_offering co
    ON co.session_id = s.session_id
  LEFT OUTER JOIN lsx.elsp_class_attendance ca
    ON ca.course_id = co.id
  LEFT OUTER JOIN its.account ac
    ON ca.student_number = ac.id

Many thanks for your help.

Personally i don't like the way you are doing this. I see an Inner Join (s with co) a Cross Join (with ac) and one Left Outer (with ca). Don't use commas for Joins, use the corresponding Join clause. — Giannis Paraskevopoulos, Oct 14 '13 at 14:13
Thanks for looking - essentially I need to join the lsx.elsp_class_attendance table on both student_id and course_id, but there may not be any attendance data, hence the need for an outer join. — Andy Flanagan, Oct 14 '13 at 14:53

MJB · Accepted Answer · 2013-10-14T15:11:11.543

You may have misunderstood the comment above. Generally people do not mix the multiple FROM tables with JOIN syntax.

I think you should write it this way instead. It should be easier to follow this way.

SELECT s.session_name semester
FROM lsx.elsp_session s
  INNER JOIN lsx.elsp_course_offering co
    ON co.session_id = s.session_id
  INNER JOIN its.account ac
    ON ca.student_number = ac.id
  LEFT OUTER JOIN lsx.elsp_class_attendance ca
    ON ca.course_id = co.id

and then see if you get syntax errors.

ORA-00904 when using OUTER JOIN with 3 tables in clause

1 Answers1