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 said nothing about SubjectID, it's StudyID I'm referring to, which is a key of neither tmpq_bst nor tblEnrollment.  It's nothing to do with Access per se, but a basic property of the  database relational model.  A join on two non-key columns represents a binary relationship type.  A binary relationship type is modelled in one way only, which is by its resolution into two unary relationship types by a separate table (StudentCourses in my earlier example is a case in point).   In a unary relationship type the referenced column must be a candidate key, not necessarily the primary key note, of one table, while the referencing column is its corresponding foreign key.  Note also that 'table' does not necessarily mean a base table, but also the result table of a query.

Answer 2

By splitting the query into parts I got a clearer error, seemingly prompted by use of table aliases.

First, qry1:

select * from tmpq_bst left join tblSubjects on tmpq_bst.[Participant ID] = trim(tblSubjects.UserSelectedSubjectID);

works fine.

Then

select * from qry1 LEFT JOIN tblEnrollment ON qry1.studyid = tblEnrollment.StudyID and qry1.SubjectID =tblEnrollment.studyid;

works as intended, but if I add an alias on either side of the join, e.g.,

select * from qry1 as q1 LEFT JOIN tblEnrollment ON (qry1.studyid = tblEnrollment.StudyID and qry1.SubjectID =tblEnrollment.studyid);

it produces "Syntax error in JOIN operation."

Table aliases definitely work in other contexts in Access; I'm not sure why they are a problem here.  Putting parentheses around (qry1 as q1) doesn't help.

However, dropping the aliases from my original query still leaves a reported JOIN error:

select tblSubjects.SubjectID, EnrollmentID, tmpq_bst.* from

( tmpq_bst left join

       tblSubjects on tblSubjects.UserSelectedSubjectID = tmpq_bst.[Participant ID])

       LEFT JOIN tblEnrollment  ON (tblEnrollment.SubjectID = tblSubjects.SubjectID AND tblEnrollment.StudyID=tmpq_bst.StudyID);

Of course, there might be something else wrong with that last select expression.

Answer 3

but if I add an alias on either side of the join, e.g.,

select * from qry1 as q1 LEFT JOIN tblEnrollment ON (qry1.studyid = tblEnrollment.StudyID and qry1.SubjectID =tblEnrollment.studyid);

it produces "Syntax error in JOIN operation."

It should either be:

SELECT * from qry1 LEFT JOIN tblEnrollment

ON qry1.studyid = tblEnrollment.StudyID

AND qry1.SubjectID =tblEnrollment.studyid;

or:

SELECT * from qry1 AS q1 LEFT JOIN tblEnrollment

ON q1.studyid = tblEnrollment.StudyID

AND q1.SubjectID =tblEnrollment.studyid;

To avoid ambiguous joins in a single query, one table can be joined to a subquery, e.g. using tables from my DatabaseBasics demo as an example:

SELECT *

FROM Contacts LEFT JOIN

    (SELECT *

     FROM ContactEmployers LEFT JOIN Employers

     ON ContactEmployers.EmployerID = Employers.EmployerID) AS Q1

ON Contacts.ContactID = Q1.ContactID;

Answer 4

Thank you.  I thought by just adding the alias I was testing a minimal change, but apparently I have to use the alias if I introduce it.

So

SELECT * from qry1 AS q1 LEFT JOIN tblEnrollment

ON q1.studyid = tblEnrollment.StudyID

AND q1.SubjectID =tblEnrollment.studyid;

works, and it still works if I substitute the SQL that defines qry1 as a subquery:

SELECT * from (select * from tmpq_bst left join tblSubjects on tmpq_bst.[Participant ID] = trim(tblSubjects.UserSelectedSubjectID)) AS q1 LEFT JOIN tblEnrollment

ON q1.studyid = tblEnrollment.StudyID

AND q1.SubjectID =tblEnrollment.studyid;

But more direct versions fail:

SELECT * from (tmpq_bst left join tblSubjects on tmpq_bst.[Participant ID] = trim(tblSubjects.UserSelectedSubjectID)) AS q1 LEFT JOIN tblEnrollment

ON q1.studyid = tblEnrollment.StudyID

AND q1.SubjectID =tblEnrollment.studyid;

I'm not sure if the AS q1 after (x LEFT JOIN y ON ...) is OK in regular or Access SQL, so I tried eliminating it.  But the following fails too:

SELECT * from (tmpq_bst left join tblSubjects on tmpq_bst.[Participant ID] = trim(tblSubjects.UserSelectedSubjectID)) LEFT JOIN tblEnrollment

ON (tmpq_bst.studyid = tblEnrollment.StudyID

AND tblSubjects.SubjectID =tblEnrollment.studyid);

That last SELECT is more or less where I started.

Answer 5

As you've found, the solution is to join two queries, or a table and a subquery.

If we take the tables from my earlier example, and try to use two LEFT JOINs with a view to returning all contacts, whether or not employed, and all employers whether or not they have any contacts as employees:

SELECT *

FROM Employers LEFT JOIN (Contacts LEFT JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID;

This will not work because the 'preserved' tables, Employers and Contacts are in a many-to-many relationship type modelled by ContactEmployers.  The subquery can use a RIGHT JOIN, which would return employers without employees, but not unemployed contacts.  To return both a UNION operation is used:

SELECT FirstName, LastName, Employer

FROM Employers LEFT JOIN (Contacts RIGHT JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID

UNION

SELECT FirstName, LastName, Employer

FROM Employers RIGHT JOIN (Contacts LEFT JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID;