I get this error for the below code. "Invalied identifier" Any suggestions?
SELECT subjects.neptuncode as "neptuncode" , subjects.subjectname as "subjectname" , enrollments.examdate as "examdate" FROM subjects, enrollments LEFT OUTER JOIN enrollments ON enrollments.subject = subjects.subject_ID HAVING COUNT(enrollments.enrollmentdate) = 1 ORDER BY subjects.neptuncode,subjects.subjectname,enrollments.examdate;
UnhandledExcepSean answered question
I think the query you want is:
SELECT s.neptuncode, s.subjectname, MAX(e.examdate) as "examdate" FROM subjects s JOIN enrollments e ON e.subject = s.subject_ID GROUP BY s.neptuncode, s.subjectname HAVING COUNT(*) = 1 ORDER BY s.neptuncode, s.subjectname;
This returns the exam date for subjects that have only one enrollee.
- Never use commas in the
FROMclause. Always use proper explicit
LEFT JOINis not needed. Your
HAVINGclause suggests that you want exactly one match.
- You should be using
GROUP BY; the
HAVINGpresupposes that you want to aggregate.
- There is no need to include the exam date in the
ORDER BY, because there is only one row per neptun code and subject.
Gordon Linoff posted this
Have an answer?