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?

Although I use it sparingly, I believe this is a time to use the query designer ^_^ — Matt Donnan, Aug 16 '12 at 12:31

score 3 · Answer 1 · answered Aug 16 '12 at 12:41

3

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.

answered Aug 16 '12 at 12:41

Fionnuala

90,370
7
114
152

If I take out only the join I mentioned, it works. Also, if I take out the join of the Table StudentContracts and the Joined table, leaving only the StudentContracts table with the rest, it works. The only problem is this one join but I'm lost as to why it doesn't work. Design view states I have ambiguous outer joints on this bit though. So I guess this is where I should start now. – Eyowzitgoin Aug 16 '12 at 12:50
I actually had it all pointing LEFT to begin with but in my attempts to find my problem, I shifted the code around, testing what would and wouldn't work. The result of all that switching was the above code which worked in every respect but the one line of code. I will switch it back once I'm sure I've found the solution. – Eyowzitgoin Aug 16 '12 at 13:02
Do you have a problem with using the query design window? – Fionnuala Aug 16 '12 at 13:03
For the whole thing, yes. For the small part, no. Partly because the bigger it gets, the more confused I get irregardless of what I use and partly because I want to understand how to write SQL without the aid of any software (it's also the only way I'll really learn). Using it I believe I've found my problem, but I'm unsure and have to let my brain process it a bit. The tables Classes and StudentClasses are related in the db as a "1 Classes to many StudentClasses" which is an outer join already so the inner join failed. Changing that join to a "left" should work then. Am I right? – Eyowzitgoin Aug 16 '12 at 13:10
1

It is almost impossible to advise on joins without the complete sql, sample data and the required outcome. In general, Access can be fussy about mixing joins, but you can work around with derived tables and subqueries. In the past, i have found it useful to build uo small sections and save them as queries and then use the saved queries to build a more complex query. You can then replace the query name with the query sql, however, this is only useful when you are sure you need derived tables. – Fionnuala Aug 16 '12 at 13:21

score 0 · Accepted Answer · edited May 23 '17 at 12:14

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!

score 0 · Answer 3 · answered Aug 16 '12 at 14:49

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.)

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

3 Answers3