Query returning syntax error in FROM clause

Question

I have 3 tables with various columns:

tableA = id(PK) & name columns
tableB = id(PK), A_ID(foreign key to tableA), name, address, etc columns
tableC = id(PK), A_ID(foreign key to tableA), name columns

I’m trying to use the following query to retrieve values from certain columns within all tables based on tableA name = ‘something’, but always returning syntax errors.

“SELECT tableA.name, tableB.name, tableB.address, tableC.name FROM 
tableA, tableB, tableC JOIN tableB ON tableA.id = tableB.A_ID JOIN tableC 
ON tableA.id = tableC.A_ID WHERE tableA.name = ‘something’”

Robert · Accepted Answer · 2013-07-30 16:03:05Z

2

You have to remove tables from from statement if you want to use join syntax

SELECT tableA.name, tableB.name, tableB.address, tableC.name 
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.A_ID 
INNER JOIN tableC ON tableA.id = tableC.A_ID 
WHERE tableA.name = 'something'

I suggest you to use aliases, the code could be more readable:

SELECT A.name, B.name, B.address, C.name 
FROM tableA A
INNER JOIN tableB B ON A.id = B.A_ID 
INNER JOIN tableC C ON A.id = C.A_ID 
WHERE A.name = 'something'

edited Jul 30, 2013 at 16:03

answered Jul 30, 2013 at 15:46

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Over a year ago

Try with INNER JOIN as above.

HansUp · Accepted Answer · 2013-07-30 16:01:09Z

1

Ms Access requires you to specify the type of join: INNER; LEFT; or RIGHT. Access does not recognize just JOIN as a synonym for INNER JOIN.

A query which includes more than one join requires parentheses in the FROM clause.

I also changed your quote characters to plain " and '. The sample query included type-setting quotes. I don't know if they are present in the actual SQL, but I would avoid them.

SELECT tableA.name, tableB.name, tableB.address, tableC.name
FROM 
    (tableA
    INNER JOIN tableB
    ON tableA.id = tableB.A_ID)
    INNER JOIN tableC
    ON tableA.id = tableC.A_ID
WHERE tableA.name = 'something'

If you have the full version of Access available, use the query designer to set up the joins. The designer knows the syntax rules which keep the db engine happy.

edited Jul 30, 2013 at 16:01

answered Jul 30, 2013 at 15:54

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

3 Comments

KFP Over a year ago

Thanks, that was my problem, no parantheses in my join. I changed it to INNER JOIN, but was using JOIN because I had read that 'joins were inner joins by default. join statements are the same as inner join statements'....Learning C# by Jesse Liberty.

HansUp Over a year ago

That is true with many databases but not Access. Your life will be easier if you can use the Access query designer. If you don't have Access, it might be worthwhile to install an evaluation version.

KFP Over a year ago

I'm running this from a winforms project and using a OleDbDataReader to retrieve records.

Hedinn · Accepted Answer · 2013-07-30 15:50:26Z

0

otherwise you could do it like:

SELECT tableA.name, tableB.name, tableB.address, tableC.name 
FROM tableA, tableB, tableC 
WHERE tableA.id = tableB.A_ID AND tableA.id = tableC.A_ID 
AND tableA.name = ‘something

answered Jul 30, 2013 at 15:50

Hedinn

8644 silver badges7 bronze badges

Comments

ASr.. · Accepted Answer · 2013-07-30 15:57:04Z

0

Parado's answer is correct..

Moreover you can simplify the length of the query by using aliases for the table names.. And it is good practice too..

select a.name, b.name, b.address from tableA a join tableB b on a.id = b.A_ID

answered Jul 30, 2013 at 15:57

ASr..

4682 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Query returning syntax error in FROM clause

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related