Link to home
Start Free TrialLog in
Avatar of intoxicated_curveball
intoxicated_curveball

asked on

MS ACCESS query multiple tables, and a JOIN

For some reason this statement is not valid in MS ACCESS SQL

SELECT * FROM (TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name), TABLE_C AS C

But this is:

SELECT * FROM (TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name), TABLE_C AS C

And this is:

SELECT * FROM TABLE_A AS A, TABLE_B AS B, TABLE_C AS C

Why?
Avatar of tankergoblin
tankergoblin

i think the right way is

SELECT example.Name AS table_name
FROM example
i think you cannot change the tablename like that...
you can only change field name..

example below:

select employeeid as employeeno from employee
Avatar of Dale Fye
First off, with this type of query, you really don't want to SELECT * as you will have at least two fields ( the join fields) with the same name.

Second, your first two examples are identical (see below), what did you really mean?
SELECT * FROM (TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name), TABLE_C AS C
SELECT * FROM (TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name), TABLE_C AS C

Open in new window

Those should work, as should:
SELECT * FROM TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name, TABLE_C AS C

Open in new window

or
SELECT * FROM TABLE_C AS C, TABLE_A AS A LEFT JOIN TABLE_B AS B ON A.Name = B.Name

Open in new window

but when you start adding a second join, then Access is very picky about how you wrap the joins in parenthesis.
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm honestly not sure why you selected hnasr's comments as the answer to your question.  All of the syntax examples that I posted in the previous post worked just fine running in Access 2007.

However, as I indicated, use of "SELECT *" when joining tables will result in duplicate columns.  You should explicitly state which fields you want from each table.
Avatar of intoxicated_curveball

ASKER

Fyed, your solution does not work in MS ACCESS (might work in MySQL or MS SQL, but not Access).

As for using * - it was merely to make the example more simple to follow, since it wasn't related to the problem.
What version of Access are YOU using?  I've got Access 2007, 2010, and 2013 Desktop versions running on various computers in my office, and that syntax worked properly on all three.
I'M using both 2010 and 2007.

'JOIN Expression not supported'.
That's bizarre!

Do you have the SQL Server Compatible Syntax (ANSI 92) checked for the current database?

This setting is found in the Options -> Object Designers - Query design; I don't.
Not checked for me either.
Now I'm totally confused.

Can you put together a database with a sample of your Table_A, Table_B, and Table_C data so I can test with your data.

I hadn't thought about it before, but "Name" is a reserved word, you might want to try it again and wrap your field [Name] in brackets everywhere that field name is used.

I was using a numeric field, so that may have had something to do with it as well.