Search code examples
sqlms-accessjoin

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

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?

Solution

  • 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!