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?
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
tankergoblin

i think the right way is

SELECT example.Name AS table_name
FROM example
tankergoblin

i think you cannot change the tablename like that...
you can only change field name..

example below:

select employeeid as employeeno from employee
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
intoxicated_curveball

ASKER
I'M using both 2010 and 2007.

'JOIN Expression not supported'.
Dale Fye

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

ASKER
Not checked for me either.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Fye

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.