Left Join On And clause not supported

Question

I've looked into various posts (this one, that one and this other one) and thought I got the answer.

After a LEFT JOIN I may add an ON [condition] AND [other condition] (I've also tried WHERE). But computer says no. Access keeps saying the join expression is not supported.

Consider the student_records table below:

STUDENTCODE  | SEMESTERINDEX
12345        | 20112
12345        | 20113
12345        | 20121
67890        | 0
67890        | 20111
67890        | 20112

I want to find the minimum SEMESTERINDEX for each student from my students table, that's above 20001. (Records below may be erroneous and the 0 and 1 SEMESTERINDEX is used for transferred credits.)

I'm using access so there are VBA functions inside the SQL. There's several more tables I'm joining too, I'm quoting the whole query.

SELECT students.STUDENTCODE, prefixes.PREFIXNAMEENG, 
students.STUDENTNAMEENG, students.STUDENTSURNAMEENG, levels.level_name, programs.PROGRAMNAMEENG, calendars.calendar_load, 
MAX(student_records.SEMESTERINDEX) AS latest_semester, MIN(student_records.SEMESTERINDEX) AS intake_semester,
FROM student_records LEFT JOIN (
    (
        (
            (
                (students LEFT JOIN prefixes ON students.PREFIXID = prefixes.PREFIXID) 
                LEFT JOIN levels ON students.LEVELID = levels.level_id) 
            LEFT JOIN programs ON students.PROGRAMID = programs.PROGRAMID) 
        LEFT JOIN calendar_conversion ON students.SCHEDULEGROUPID = calendar_conversion.schedule_id) 
    LEFT JOIN calendars ON calendar_conversion.calendar_id = calendars.calendar_id) ON student_records.STUDENTCODE = students.STUDENTCODE AND student_records.SEMESTERINDEX> 2001
GROUP BY students.STUDENTCODE, prefixes.PREFIXNAMEENG, students.STUDENTNAMEENG, students.STUDENTSURNAMEENG, levels.level_name, programs.PROGRAMNAMEENG, calendars.calendar_load;

So did I misplace the AND student_records.SEMESTERINDEX > 2001?

Hogan · Accepted Answer · 2016-09-11T03:07:28.730

0

oh my save me from these parenthesis and crazy indenting.

Here is how you do it. All the parenthesis don't matter in SQL

SELECT 
  students.STUDENTCODE, 
  prefixes.PREFIXNAMEENG, 
  students.STUDENTNAMEENG, 
  students.STUDENTSURNAMEENG, 
  levels.level_name, 
  programs.PROGRAMNAMEENG, 
  calendars.calendar_load, 
  minmax.latest_semester, 
  minmax.intake_semester,
FROM student_records 
LEFT JOIN (
   SELECT 
     studentcode,
     MAX(student_records.SEMESTERINDEX) AS latest_semester, 
     MIN(student_records.SEMESTERINDEX) AS intake_semester
   FROM students
   WHERE students.STUDENTCODE > 2001
   GROUP BY studentcode
) as MinMax ON student_records.STUDENTCODE = minmax.STUDENTCODE
LEFT JOIN students ON student_records.STUDENTCODE = students.STUDENTCODE 
LEFT JOIN prefixes ON students.PREFIXID = prefixes.PREFIXID
LEFT JOIN levels ON students.LEVELID = levels.level_id
LEFT JOIN programs ON students.PROGRAMID = programs.PROGRAMID
LEFT JOIN calendar_conversion ON students.SCHEDULEGROUPID = calendar_conversion.schedule_id
LEFT JOIN calendars ON calendar_conversion.calendar_id = calendars.calendar_id

This is called a sub-query in sql it allows you to perform your grouping on a sub-set and then join that back to the rest of the data.

I think you went wrong thinking there was something about the join that needed a filter -- in fact it is the data that you were joining to that needed to be filtered.

edited Sep 11 '16 at 03:07

answered Sep 11 '16 at 03:03

Hogan

69,564
10
76
117

1

"All the parenthesis don't matter in SQL" they do in Access. Access is annoyingly picky about requiring that joined tables be put in parentheses. – D Stanley Sep 11 '16 at 03:07
@DStanley -- nope. Access will show you queries with them, but if you take them out and send them to the Jet Engine it will work just fine -- it is an SQL system after all. It is the Query Builder engine that is putting in all that crap. – Hogan Sep 11 '16 at 03:08
I see, so basically you calculate the min and max per studentcode in the student_record, wrap the result in minmax table, and join that table to student_record. Right? – neydroydrec Sep 11 '16 at 03:10
@DStanley - the only time you would need them in SQL is if you are combining LEFT and RIGHT joins in the same query and for some perverted reason want to put them in an order that is not "natural". – Hogan Sep 11 '16 at 03:10
@Benjamin - if it makes you feel better to call it "wrapping", sure. SQL programers would understand what you meant better if you call it a sub-query. So we put the result in a sub-query and joined that to the main query. – Hogan Sep 11 '16 at 03:12
I took your solution though I implemented it differently: I've used another query in Access and called the results of that query in the main query (instead of a writing the sub-query aka wrapping ;) in the main query). And the reason for that was the headache Access tried giving me with brackets all over the code. – neydroydrec Sep 11 '16 at 05:16
@Benjamin - Excellent, that might be the only way to implement it in Access, I didn't have a test environment to test with. – Hogan Sep 12 '16 at 14:25

Left Join On And clause not supported

1 Answers1