Right Join works, Inner Join works, Left join doesn't? Why?

Question

I have some SQL joining multiple tables in Access. When attempting to run it, I get an error in the "JOIN" (specifically "JOIN expression not supported"). I believe I have narrowed down the problem to one join but why it isn't working doesn't make any sense to me. The original full SQL FROM clause is thus:

FROM  (
     (
      Customers RIGHT JOIN 
            (
            Sales LEFT JOIN SaleType ON Sales.SalesForID = SaleType.SalesForID
            ) 
      ON Customers.CustomerID = Sales.CustomerID
     ) LEFT JOIN 
          (
           StudentContracts LEFT JOIN
               (
               StudentsClasses INNER JOIN Classes ON StudentsClasses.ClassID = Classes.ClassID
                )
            ON StudentContracts.CustomerID = StudentsClasses.CustomerID
           ) 
       ON Customers.CustomerID = StudentContracts.CustomerID   
 )

The part I believe the query fails is on this "LEFT" join:

(
  StudentContracts LEFT JOIN
          (
          StudentsClasses INNER JOIN Classes ON StudentsClasses.ClassID = Classes.ClassID
          )
   ON StudentContracts.CustomerID = StudentsClasses.CustomerID
)

I've tried switching the the "LEFT" to an "INNER" and it works. I've switched it to a "RIGHT" and it works. Why will it not work for a "LEFT" join but work for the others? What I need is a result showing the records in joined "Classes" table linked to the StudentContracts but also the StudentContracts without a record in the Classes table. As per the answer on this post: Difference between left join and right join in SQL Server I am fairly certain I want a left join and this should work.

What am I missing here?

Answer 1

You have far too many parentheses and things are not in the right order. It would be easiest to build this in the query design window of MS Access, and then everything will be generated for you, you can switch to SQL view to see.

For example, the above should read something like:

SELECT * 
FROM (Customers 
RIGHT JOIN Sales 
ON Customers.CustomerID = Sales.CustomerID)
LEFT JOIN SaleType 
ON Sales.SalesForID = SaleType.SalesForID

It is the convention that RIGHT JOIN is avoided because they can easily be written as LEFT JOIN, so it avoids any confusion.

Answer 2

Guess I should answer my own Q since the two suggestions weren't really answers but suggestions on trouble-shooting (which worked) and I've found my answer now.

It was indeed a problem with a previously defined join. StudentsClasses is joined to Classes on a many to one basis in the DB relationships. This is how most connections throughout the database will see these two tables as being linked. It is only in this case where I wish for them to be joined slightly differently. When my SQL asked for an inner join, it seems to have confused the access program as to what I meant (inner or outer join). By changing that to the appropriate outer join, the program started running fine.

While an inner join is really what I wanted, in this case it didn't really matter since the side with the extra records will be reduced by the other joins and thus won't produce any records without a match in the Class table anyway (at least as far as I can see).

So, many thanks to Remou and Matt Donnan for pointing me in the right direction!

Answer 3

Access is picky about bracketing joins. Typically, it wants to see a query written like this:

FROM (((((Sales
LEFT JOIN Customers ON Sales.CustomerID = Customers.CustomerID)
LEFT JOIN SaleType ON Sales.SalesForID = SaleType.SalesForID)
LEFT JOIN StudentContracts ON Customers.CustomerID = StudentContracts.CustomerID)
LEFT JOIN StudentsClasses ON StudentContracts.CustomerID = StudentsClasses.CustomerID)
LEFT JOIN Classes ON StudentsClasses = Classes.ClassID)

Without knowing the exact structure of your tables, it is pretty hard to know if this will give you desired output; however I expect something similar to this would work. You said:

I want a result showing the records in joined "Classes" table linked to the StudentContracts but also the StudentContracts without a record in the Classes table

This appears to me to be a convoluted way of saying StudentContracts LEFT JOIN Classes

(Undoubtedly someone will correct me if I am wrong.)