Wsrtjtyk

I get this error for the below code.

Invalid 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;

Change this line

FROM subjects, enrollments

to

FROM subjects

You shouldn't mix the old style of joins (comma delimited) and explicit joins. Really you shouldn't use comma delimited joins as they were essentially replaced many, many years ago.

FROM subjects, enrollments

There's your problem.
Change that to FROM subjects - problem solved.

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.

Notes:

• 
  Never use commas in the FROM clause. Always use proper explicit JOIN syntax.


• A LEFT JOIN is not needed. Your HAVING clause suggests that you want exactly one match.


• You should be using GROUP BY; the HAVING presupposes 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.