Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

"join expression not supported" in Access

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?

like image 946
Mandeep Singh Avatar asked May 17 '13 11:05

Mandeep Singh


People also ask

Does Access support join?

Access does not explicitly support full outer joins, but you can achieve the same effect by using a union query.

How do you enter an expression in Access?

Select the control into which you want to enter an expression. If the Property Sheet is not already displayed, press F4 to display it. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression.

Which join is not supported in SQL?

It is something to do with the left join on PNLTERM/TERMS; as it works with a inner and right join but not with a left join. The error is 'Join expression not supported'.

What are join properties in Access?

Setting the join properties, also known as join type, will determine what records will be displayed when the query is executed. There are three relationship join types available in relational databases: Inner join. Left outer join.


2 Answers

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

like image 74
Mia Avatar answered Nov 12 '22 09:11

Mia


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'
like image 25
Gord Thompson Avatar answered Nov 12 '22 09:11

Gord Thompson