Access SQL: trouble with nested LEFT JOIN and multiple ON conditions

Question

I would like to do a left join using 2 field comparisons, but I can't get it to work, at least when there is some nesting of the joins.  The multiple comparisons are in the last line:

select sub.SubjectID, EnrollmentID, b.* from ( tmpq_bst as b left join

       tblSubjects as sub on sub.UserSelectedSubjectID = b.[Participant ID])

       LEFT JOIN tblEnrollment as enr ON (enr.SubjectID = sub.SubjectID AND enr.StudyID=b.StudyID);

gives "JOIN expression not supported".  I've tried many variations of parentheses placement, as well as using AND ON, without luck.

Using Access 2016, though I also need it to run in Access 2010 (both 32bit), using the Jet engine I think (split database but not a frontend to a serious SQL DB; mdb files),

If I use only one of the 2 final AND conditions the query runs, though of course it's asking for something different.

select sub.SubjectID, EnrollmentID, b.* from ( tmpq_bst as b left join

       tblSubjects as sub on sub.UserSelectedSubjectID = b.[Participant ID])

       LEFT JOIN tblEnrollment as enr ON enr.SubjectID = sub.SubjectID

where enr.StudyID=b.StudyID;

also runs, but I think the semantics are again different from what I want, since the last query excludes records from tmpq_bst that lack a match in tblEnrollment.

I've seen various references to Access SQL being non-standard, including a statement that AND is not supported in combination with ON, and that extra parentheses are needed in various places (around the tables in FROM and for the ON conditions).  But isn't there some way to make this work?

The MS documentation for Access SQL is notably short on specifics.  For example, the definition of SELECT include a reference to "tableexpression", but I can found no syntax for tableexpression anywhere in the documentation.  I suppose if I were being literal the syntax given for join expressions there only includes a single comparison after ON, so maybe I'm just out of luck.

This is for VBA code, though I've been testing by entering expressions interactively into SQL view for a query in the main Access app.

Answer 1

I'm not clear what you are trying to achieve here, but it looks like you might be trying to return data from two tables in a many-to-many relationship type modelled by a third table, but with all values from both referenced tables returned regardless of whether there is a row in the third table which maps to the relevant rows in both referenced tables.  If so you would need to return the Cartesian product  of the two referenced tables, which is done by including both tables in the query, but without a specific join.  You can then indicate which of the rows represent an actual relationship value, and which do not, by means of the EXISTS predicate and a subquery.  The query below is an example which used data from my StudentCourses demo:

SELECT StudentID, FirstName, LastName, CourseName,

EXISTS(SELECT *

            FROM StudentCourses

            WHERE StudentCourses.StudentID = Students.StudentID

            AND StudentCourses.CourseID = Courses.CourseID) AS Enrolled

FROM Students, Courses

ORDER BY StudentID,CourseName;

This would return the following result table in the demo:

StudentID    FirstName    LastName    CourseName    Enrolled

1                  John             Brown           Chemistry         False

1                  John             Brown           English             True

1                  John             Brown           Geography       True

1                  John             Brown           History             True

1                  John             Brown           Maths               False

1                  John             Brown           Physics             False

2                 Jane              Green            Chemistry         True

2                 Jane              Green            English             False

2                 Jane              Green            Geography      False

2                 Jane              Green            History             False

2                 Jane              Green            Maths               True

2                 Jane              Green             Physics            True

3                 Jim                White            Chemistry        False

3                 Jim                White            English            True

3                 Jim                White            Geography      False

3                 Jim                White            History            True

3                 Jim                White            Maths              True

3                 Jim                White            Physics            False

9                Joanna           Black             Chemistry        True

9                Joanna           Black             English            False

9                Joanna           Black             Geography      False

9                Joanna           Black             History            True

9                Joanna           Black             Maths              True

9                Joanna           Black             Physics             True

You'll find the demo in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

Answer 2

I think my problem is considerably different.

I want every record in tmpq_bst

Each record has a [Participant ID] field which has a 1:1 match with a UserSelectedSubjectID in tblSubjects.  The latter table also has a SubjectID.

I want to pull in the data from tblEnrollment which has the same SubjectID (in principle the relation between tblSubjects and tblEnrollment is 1:N, though in practice usually 1:1).

In case there are multiple matches on SubjectID in tblEnrollment I want only the one with StudyID matching that of tmpq_bst.

So in the end, each resulting record will be a record from tmpq_bst, with the single matching record from tblSubjects appended if available, and with a single matching record from tblEnrollment if available.  Assuring that only a single record from tblEnrollment matches requires the equality of 2 different fields.

But the query is actually just representative of a type of problem, and is itself pulled out of a larger query.  I'd like to nest left joins and join on multiple conditions.

Is your answer an implicit statement that multiple ON conditions are just not going to work?

Answer 3

I tried to construct the query graphically to see what SQL Access would generate:

No dice:

The message does suggest a work-around....

Answer 4

I'm afraid I cannot make any sense of your logical model, but I'd suggest you try including two instances of tblEnrollment in the query and then join tblSubjects to one instance, and tmpq_bst to the other.  That would impose some linearity on the query, but it would still leave you with a join on two non-key columns (StudyID), which is not really valid.

Answer 5

The columns involved are keys (subjectid even has a key icon in the tblSubjects in the screenshot) although admittedly not the key for tblEnrollment.  StudyID is the key to tblStudies, which tmpq_bst references, although it does not appear in the diagram.

But why does it matter whether the fields are keys?  I'm unaware that SQL required the fields to be keys.  Is that an Access-specific restriction?