Invalid identifier when using left outer join in SQL

1192 views sql
8

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;

answered question

You should explain what you want to do, given that your query has multiple errors. A database tag is also helpful.

3 Answers

1

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.

posted this
3

FROM subjects, enrollments

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

posted this
13

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.

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories