"join expression not supported" in Access

Question

I am writing a SQL query with inner join as this

select * from (table1 inner join table2 on table1.city = table2.code)
   inner join table3 on table3.col1 = 5 and table3.col2 = 'Hello'

This giving me the error "Join expression not supported".

However, if I change the query like this then there is no error

select * from (table1 inner join table2 on table1.city = table2.code)
   inner join table3 on table3.col1 = [SomeColumn] and table3.col2 = [SomeColumn]

Why is Access giving me an error on the first query?

Lysol

Answer 1

Why is Access giving me an error on the first query?

Well, like the error message says, that form of a JOIN expression is not supported.

You might want to try the following:

SELECT * FROM table1, table2, table3 
WHERE table1.city=table2.code AND table3.col1=5 AND table3.col2='Hello'

Meath

Well this is an alternate solution, instead of using join that will not help if there are more table, but work on this situation, thanks – Lysol

Answer 2

Very late but I had a similar problem with JOIN expressions on MS Access, like described here Access sometimes needs everything inside the ON part of the query to be inside parenthesis, ie:

SELECT ... JOIN <table> ON (everything here inside the parenthesis) WHERE ...

Selestina

This is the actual answer. – Gon

Late to the party, but this should be the accepted solution. – Martyrdom

And if this still doesn't work? Keeps telling me it's still not supported. – Imaimage

Answer 3

While the other answers work for INNER JOIN, they did not work for LEFT JOIN for me.

-- error: join expression not supported
SELECT table1.*, table2.*
FROM table1
LEFT JOIN table1 ON (table1.id = table2.t1id AND table1.fld = 'Hello')

The solution was to remove the 'Hello' condition out of the LEFT JOIN. The trick is to add " OR table1.fld IS NULL " to still have all results from table1.

-- working as expected
SELECT table1.*, table2.*
FROM table1
LEFT JOIN table1 ON (table1.id = table2.t1id)
WHERE table1.fld = 'Hello' OR table1.fld IS NULL

Filial