Wednesday, October 10, 2007

Multiple LEFT JOINs in MS Access

I had forgotten about this one. In Microsoft Access, if you want to do more than one LEFT JOIN, you have to use parenthesis in the FROM clause. So, for example, instead of just plain old:

SELECT a.columna, b.columnb, c.columnc
FROM tablea AS a LEFT JOIN tableb AS b ON a.id = b.id LEFT JOIN tablec AS c ON a.id = c.id

you would have to do the following:

SELECT a.columna, b.columnb, c.columnc
FROM ((tablea AS a) LEFT JOIN tableb AS b ON a.id = b.id) LEFT JOIN tablec AS c ON a.id = c.id

Otherwise, you get a "Missing Operator" error. Stupid Access.

--Update 10/29/2009--
Incidentally, this should work with other sorts of joins as well.

160 comments:

Anonymous said...

thank you for that

been dragging my hair out for 3 hours until I found your post (frickin crappy MS Access @#$%^)

Anonymous said...

I HATE MS ACCESS!
I LOVE YOU!

Lipis said...

Thanx mate...!! You saved me :)

Anonymous said...

This was a godsend. Plain language for those who aren't SQL gurus.

Thanks!

Anonymous said...

damn, i lost my sleeping hour just debugging my access sql... damn u ms access...

Love u dude!!!!

lets rock the search engine:

multiple left join ms access multiple left join ms access multiple left join ms access multiple left join ms access multiple left join ms access multiple left join ms access

Thaxor said...

not just left joins, had to add () for inner join too

thanks :)

Anonymous said...

Thanks saved my bacon ...... PPT

Anonymous said...

I would have lost hours if I weren't lazy enough to google this one... There IS a God. Thank you!

Anonymous said...

Spent a couple of hours on this until I read your post. Can't believe Access is so petty! Thanks, man!

ADINSX said...

This was a very tricky problem. Seems like an arbitrary, frustrating deviation from standard SQL, coupled with an indecipherable error message. Thanks a lot for the help.

Ian said...

Nice one, if only MS could stick to standard SQL!

Philly Murder Contest said...

thank you so much I would have never guessed that

Anonymous said...

Thank you, thank you, thank you!!!!

RickyRandom said...

Well played - helped me out. I live in sql and only needed to use access for a client of mine so I could set them up a little mini-app.

A tip to the rest of you out there who said they wasted hours on this - you should really search the web much much sooner than that.

Access blows me.

Farid said...

guess what, i was also trying to do 2 inner join in access had the same error did a google search "multiple inner join msaccess" and yours is the first most likely solution and it worked.
thanks

Anonymous said...

I'm glad I looked this up after about 10 minutes of confusion. Thank you so much!

Robert John said...

You saved me!!!!

Thanks a lot. I was about to give up on this multiple left joins.

sheesh!

haha!

Anonymous said...

"Nice one, if only MS could stick to standard SQL!"


Correction:
I M$ only could stick to standards!!!!!! They S#ck at that!

Anonymous said...

OH MY GOODNESS! Thank you!!!!! We should just stop calling what MS Access uses 'SQL'. Howabout 'SQ HELL' or 'PLEAquel'

Lakshmi said...

Oh...Thank You !!!

Lejf Diecks said...
This comment has been removed by the author.
Lejf Diecks said...

THANK YOU! This is really annoying, wasted about half an hour for trying to solve with my ANSI SQL knowledge and another ten minutes to get all the paranthesis right (LEFT JOIN over 4 tables)...

Btw: You also have to put multiple paranthesis around two or more statements behind ON, e. g.

... LEFT JOIN (Adress AS ka) ON (a.Id = ka.Id AND ka.Typ='KITA') ...

Anonymous said...

Sure you've heard this a number of times, THANK YOU VERY MUCH!!!!!!!

Mackensen said...

Argh, I don't know how many hours I've wasted debugging before I found this! You are the man.

Chris said...

Nice list of 'Thanks' messages - so I'll add my thanks to the list as well :)

Guess I'm lucky in that I only wasted 10 mins on this. Was pretty sure my SQL was correct so started looking for Access quirks.....

Sam said...

Thank you, thank you!

Anonymous said...

Many thanks!!

Anonymous said...

Duuuuude

You are ace... I have been struggling with this for ages.

u the man

Cheers

ams143 said...

How to form the below query,

select PM.Prod_Name,NV.Visit_Count,ST.Quantity,RT.Prod_Code as "Rejection Reason"
from
(( Product_Master PM)
inner join Next_Visit NV on NV.Prod_Code = PM.Prod_Code)
left join Sale_Table ST on ST.Prod_Code = PM.Prod_Code
left join Rejection_Table RT on RT.Prod_Code = PM.Prod_Code

Anonymous said...

I think you need an extra set of parenths:

select PM.Prod_Name,NV.Visit_Count,ST.Quantity,RT.Prod_Code as "Rejection Reason"
from
((( Product_Master PM)
inner join Next_Visit NV on NV.Prod_Code = PM.Prod_Code)
left join Sale_Table ST on ST.Prod_Code = PM.Prod_Code)
left join Rejection_Table RT on RT.Prod_Code = PM.Prod_Code

Great tip!

ams143 said...

Thanks man.It works

Geca said...

Thank you very much. Works like a charm.

Anonymous said...

And it is still relevant even in 2009. Thanks much!

Anonymous said...

Amzing Work Man!!! Nice JOB!!! I am at a loss of words to thank you

Select 'TOM' FROM [Tasmania] said...

Hi All,

After reading this, and many brackets later... I discovered that you can join using an Oracle style syntax as follows:

SELECT * FROM Table1, Table2, Table3 WHERE Table2.ID = Table1.FK AND Table3.ID = Table2.FK

This has an advantage over the parenthesized methods when designing a correlated query... a trivial example:

SELECT * FROM Table1, Table2, Table3 WHERE Table2.ID = Table1.FK AND Table3.ID = Table2.FK AND Table3.FieldName = Table1.FK2

Hope this helps someone trying to build correlated queries and resenting the error "join type not supported"

- Tom

Arne Martin said...

Thanks N M and Tom! Top one google hit when searching for "multiple joins in ms access"...

Mathew said...

I'm working on a command pretty much identical to the one in the example (One select with two inner joins), have followed the example syntax correctly, and am still getting a "Syntax error in JOIN operation" alert that then highlights the name of the first joined query.

Any thoughts, or is this another case of Microsoft apparently singling me out to ruin my day?

Anonymous said...

Thank you very much! It's amazing how misleading some error messages are!

Could you include in the title the inner joins, too?

vardars said...

Thank you very much. This tip is really appreciated...

Anonymous said...

THANKS YOU VERY MUCH,THIS IS VERY USE AS IT SHOWS DIFFRENT NAME INSTANCE OF A TABLE SOMETHINGS LIKE A VIEW(QUERY IN MS ACCESS) IS THIS ALSO WORKS ON SQL SERVER ?

REGARDS

HOPEFUL FOR BETTER REPLY
SP

hok said...

Thanks for this top ranked page in Google, this solved my problem in 1 minute (I have a hard time with Access since I'm used to working with Oracle).

Anonymous said...

Agreed. Thanks a ton

Anonymous said...

Many thanks! This was a very helpful explanation of Access' odd syntax.

Anonymous said...

Thank you!

Alex said...

Today I woke up and saw that some data on my PC were damaged.Fortunately I could recover mdb files with help-Recovery for Access.Moreover tool made it for free and demonstrated how it working with source files having *.mdb and *.accdb extensions.

Anonymous said...

Thank you so much for this post! You just saved me as well!

Passion said...

Thanks buddy.
It's as sexy as Amma Etkinson's Boobs.

Anonymous said...

Thanks dude. Still helpfull! Greetings from Germany.

this ones going out to the searchengines out there :D
multiple left join ms access
multiple left join ms access
multiple left join ms access

Frank said...

Aloha & Mahalo!!!!

KomA said...

Tnx man.. Your post helped me a lot! Luckily, I googled It very quickly

Michael said...

Looks like your popular, found this very very helpful, thank you!

Anonymous said...

alleluiah!!!

Thanks for posting this article. Ive been working on this problem for 12 hours til I found you !!!

Anonymous said...

Thank you so much!! :D You were the first place I looked at from Google!

FourEyedJack said...

Ditto all the other comments, saved me a ton of time!

Anonymous said...

Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!! Thanks!!

Anonymous said...

Thanks a lot, N M!
M$ Access is aweful when it comes to database functionality :(

Luis F. Buelvas T. said...

Thank's. Gracias.

Luis F. Buelvas T. said...

Thanks. Gracias.

Someone from Holland! said...

Thank you very much!

Anonymous said...

Three years later and you're still helping people. Thanks for the info!

evilripper said...

thanks!!!
f.. parenthesis , i hate access too! :-D

Anonymous said...

Thanks! You saved my day. Stupid brackets !

Anonymous said...

Thanks!

Jojo Maquiling said...

Thanks your post helps me a lot. I really want to give up on this issue, but your post is on top of google hits. Now my complex joins in MSACCESS works.

Anonymous said...

Thanks! I thought I was going to have to use a bunch of VIEWs for this!

-MTEXX

Foamy Lens said...

Thank you for clarifying that. I want to punch Access in the face. Why can't Microsoft finally just stick a T-SQL backend on it, or at least give you the option about what engine to use. I hate JetSQL.

Anonymous said...

Thanks for the info! Very helpful!

Anonymous said...

keep it up dude. nice and simple

Anonymous said...

It's just like an old friend said once: "standards are so good, that everyone ha their own".

MS took that to heart when coding how Access handles multiple joins.

Jignesh said...

Thank You !

Anonymous said...

thank you, i'm loving ya

Anonymous said...

Thanks guys, it saved me a lot of time, may be it will save for someone too again and again

Anonymous said...

Merci beaucoup.

Thanks you very much (in French)

Anonymous said...

Good stuff, keeps being useful even in 2011 :)

Anonymous said...

thank you so much.. your the best!

Student of Life said...

Reiterating what others have said: THANKS! This post along saved mucho hours. But, I CANNOT figure this one out..

The query below works perfectly in SQLServer 2008R2. However, it CRASHES Access2k3. I cannot figure out how to otherwise parentesize it.

HELP!!

the SQL that works:
select ContractID, PurchaserACID, ContractReviewerACID, LoggerACID
from Contracts
left join DBConfig on (contracts.PurchaserACID = DBConfig.Key1 and dbconfig.keyname='ACIDName' )

The 3 xACID cols are nvarchar(7); some have values, some are NULL.

DBConfig is structured like this:
KeyName, Key1, Key2, Key3 (all nvarchar).

any thoughts???

SimplifyingAsp.Net said...

Can Anyone plz convert this sql server query in to MS Access Querry.This works fine in sql server.


select D.DocumentId,D.ModuleId,NJD.UserId,D.DocumentName,D.DocumentDescription,D.DocumentPath,D.TypeOfTraining, D.PlannedDuration,UTD.StartDate,UTD.EndDate,UTD.Duration,UTD.Status from DocumentsMaster D left join ModuleMaster MM on D.ModuleId=MM.ModuleId left join UserTrainingDetails UTD on D.DocumentId=UTD.DocumentCode inner join NewJoineeDetails NJD on MM.RoleId=NJD.RoleId where NJD.UserName='UserName'

SimplifyingAsp.Net said...

Can anyone plz convert this Sql Server query in to MS-Access Query.This works fine in Sql Server


select D.DocumentId,D.ModuleId,NJD.UserId,D.DocumentName,D.DocumentDescription,D.DocumentPath,D.TypeOfTraining, D.PlannedDuration,UTD.StartDate,UTD.EndDate,UTD.Duration,UTD.Status from DocumentsMaster D left join ModuleMaster MM on D.ModuleId=MM.ModuleId left join UserTrainingDetails UTD on D.DocumentId=UTD.DocumentCode inner join NewJoineeDetails NJD on MM.RoleId=NJD.RoleId where NJD.UserName='UserName'

Nisheeth K. Barthwal said...

I owe you a beer, mate.

Anonymous said...

OMG!!! Tom, the Select table1, table2, table3...etc. It was almost as good as ... all the concentration, and frustration leading up to that one moment when you hit the run button, and you are faced with all this beautiful data!!! What did they do to the SQL editor in the new MS Access? It didn't used to be so bad.

Ken Blackstein said...

YAT - Yet another thank you! I don't do much Access and your post sure saved the day. This has inspired me enough to begin posting the gotchas etc. that I've found through the years.

Very best regards
Ken

Naresh Vidhani said...

Perfect
Thanks

Anonymous said...

Thanks very much ...
Your blog is really inspiring ...

Anonymous said...

Awesome, a 4 year old post can confirm that I am still sane. Thanks man!

Daryl Behrens said...

Thank you - saved me tons of time.

Barbara said...

Tried to compare my code to suggesions for multiple joins, and I still get "Syntax error in JOIN operation. Can anyone help?
Select DISTINCTROW tblA.*, tblB.BLstName AS B1LstName, tblB.BFstName AS B1FstName,
BCopy.BLstName AS B2LstName, BCopy.BFstName AS B2FstName,
tblC.CName, tblD.DName
FROM ((((tblA) LEFT JOIN tblB ON tblA.A_M1 = tblB.B_ID)
LEFT JOIN (tblB as BCopy) ON tblA.A_M2 = BCopy.B_ID)
LEFT JOIN tblD ON tblA.To = tblD.D_ID)
LEFT JOIN tblD ON tblA.From = tblC.C_ID
WHERE tblA.A_ID = 2
GROUP BY tblA.A_ID;

Anonymous said...

was more important and helpful than any of the other training.
Thanks mate

Rails said...

You rocks man. Thanks :-)

Anonymous said...

Thanks mate! Saved my day/job! ;)

Hyrius said...

I love you so much.

Anonymous said...

I am gettign different results for the below query in MS Access and SQL server.

SELECT Count(*) AS Expr1
FROM A INNER JOIN B ON A.C1 = B.C1;

Tables A and B contains same data. A has 95757 records and B 4998 records.
I MS Access I am getting 42767 records and SQL server gives 248263.

I want to make the reult equal to MS Access in SQL server. What I have to do for that.

-- Anil

sebastian said...

Made my day!

Anonymous said...

Thank you very much! You saved me! ;-)
God bless you!!

Anonymous said...

My god, what a piece of sh*t Access is... why are people still being forced to use this?

Anonymous said...

Great my friend!!!!!!!!!!!

LateToTheGame said...

For search engine:
MULTIPLE JOIN ACCESS ANOMALY
Error message: Syntax error (missing operator) in query expression '...'

For you:
THANKS!

For Microsoft:
/"\
|\/|
| |
/'\| |/'\..
/~| | | | \
| | | | | \
| ~ ~ ~ ~| ` )
| /
\ /
\ /

Anonymous said...

Well, Im just another person who you've randomly helped!! Thaaaaaank u!!!

Anonymous said...

Damn you save me man,urgh....thank a lot

C├ędric said...

Thanks for the tip! I Haven't found it in the help.

"(...) Stupid Access."

I was saying the same to my colleague few minutes ago ;)

Anonymous said...

THANKS ALOT THIS HELPED ME TOO.

MARCEL FROM IVORY COAST

Gustavo said...

Thank you! I hate MS Access!

Gustavo said...

Thanks!

Unknown said...

Thx! 4y old but still usefull post

Anonymous said...

That was just an awesome fix for my issue. You gave exactly what I want. I have been working on different RDBMS databases without much effort but MS Access is a hell for me.. Thanks a lot once again

Paul K. said...

THANKS, you helped me with my 3 left join problem. I tryed to complement one table from two others. Now it works:
SELECT * FROM (Tab_B LEFT JOIN (Tab_C AS TC) on [Tab_B].iD = [TC].id) LEFT JOIN (Tab_A LEFT JOIN Tab_C ON [Tab_A].ID = [Tab_C].id) ON [Tab_B].iD = [Tab_A].ID;

Paul K. said...

THANKS, you helped me with my 3 left join problem. I tryed to complement one table from two others. Now it works:
SELECT * FROM (Tab_B LEFT JOIN (Tab_C AS TC) on [Tab_B].iD = [TC].id) LEFT JOIN (Tab_A LEFT JOIN Tab_C ON [Tab_A].ID = [Tab_C].id) ON [Tab_B].iD = [Tab_A].ID;

Flying Scot said...

I add my thanks to those of others! Good post!

Jonas said...

Thank you so much!

Anonymous said...

Thank you so much for saving me from hours of grief.

Anonymous said...

THANK YOU VERY MUCH! UGH - I cannot believe this information is not contained in Access Help.

You are a lifesaver.

Delomositit said...

You Super Rock Dude!!!

Anonymous said...

I AM a SQL Guru cutting my teeth on MS's 'version' (Access).

OMG, I can't believe how BAD their compiler is. That plus all the 'variants' such as SQL vs VBA (Triple "s, bangs and dots; Aaaaaagh !!!!). Now I get why MS get's bad-mouthed so much.

Many, many thanks for clearing this up for me.

BeforeLogic said...

Thanks! Made it quick and easy to find the format for Access multi-table joins...

Site looks interesting just from this single page - must check it out in-depth :)

Take care and ALL THE BEST!

Anonymous said...

Thx!

rangga adiyasa said...

Thanx bro...
It's very helpfull...coz for first time i dont understand
why multiple left join doesn't work.

Anonymous said...

Things I've learned: Start looking for online tips like this one rather than waste 2 hours on debugging a perfectly alright SQL statement which access denied, no pun intended.

Thanks for sharing your wisdom, greetings from Germany!

Buzz said...

Killer post, BTW multiple Joins requires additional ()

SELECT DataTypeSpec.Description, DataTypeName.DataTypeName, PGN.PGN, DDI.DDI_Addr
FROM (( DataTypeSpec
INNER JOIN DataTypeName
ON DataTypeSpec.DataTypeNameKey = DataTypeName.ID )

LEFT OUTER JOIN PGN
ON PGN.DataTypeSpecKey = DataTypeSpec.ID )


LEFT OUTER JOIN DDI
ON DDI.DataTypeSpecKey = DataTypeSpec.ID

Anonymous said...

Thank you, that made my day easier!

Neo said...

Thanks Man! It was a really hopeful

George

Anonymous said...

Thank You! Just wish I had come upon this post 2 days ago!! Echo "godsend"!

Anonymous said...

Thanks man!

Sometimes, someone has to deal with MS Access...crappy thing!

Anonymous said...

Thanks a bunch. Very helpful!

Anonymous said...

I shall battle Access till it bends to my will (with help from google). I shall mould it to do what the poor fools I work for desire so they may leave me alone

Anonymous said...

it. works :) thanks...

Anonymous said...

awesome! saved me a massive headache

Anonymous said...

thank u so mucccccccccchhhhhhhhhhhhhhhhhhhhhhhhhhh

Anonymous said...

OH MY GOSH!!! Thank you so much!!! Multiple outer left joins were giving me aneurysms.

You have saved my sanity sir. Had I any internets, they would be yours.

tcDiputado said...

Thank you very much! ;D

Anonymous said...

Thank you! My syntax looked correct based on my MSSQL background - you saved me a lot of troubleshooting!

Anonymous said...

Still providing useful information years later. Thank you. I found it highly useful.

Anonymous said...

Killing me for a day, didn't think I needed to google to solve my multiple LEFT Join SQL Syntax.
THANK YOU!!

Anonymous said...

Thanks!

Anonymous said...

Thanks!

Anonymous said...

Thanks!

SCOFIELD said...

Thank you, made my day :)

Anonymous said...

Thank you, thank you, thank you!
I've had something similar and you gave me the idea to use parenthesis. Now my SQL works on silly Access and I didn't spend too much on it!!

Anonymous said...

Much appreciated... Access is awful. You saved me loads of time.

Anonymous said...

Thanks, fixed my issue. Why the hell is this still happening...?

Anonymous said...

Grazie mille!!!!

Anonymous said...

As so many other have said--thanks for shedding light on what should never have been a problem!

Anonymous said...

Very hard to read in that format!

SELECT
FROM ((tbl1
LEFT JOIN tbl2 ON )
LEFT JOIN tbl3 ON )
LEFT JOIN tbl4 ON

Anonymous said...

2015 and still useful. Thx

Anonymous said...

THAT WAS VERY VERY HELPFUL! :D

Flat Irons Review said...

SQL syntax and parsing in Access is awful. Thanks a lot for this tip!

Anonymous said...

Thank you very much, saved me a lot of time :)

Anonymous said...

8 years on... this is still helpful.. you rock my friend...

Bhavesh Patel said...

Thank you.

Bhavesh Patel said...

Thank you.

Anonymous said...

Thanks!

Anonymous said...

Another 'saved' soul reporting in. Good guy OP; saving SQL noobs since 2007, big thanks ... also, i shake my fist at you ms access sql!

Peter said...

Thank you. You saved my evening!

Bruno said...

HELL JA !
Many thanks from Germany!

Anonymous said...

Thank you, this was very helpful

Mig said...

Great!

To fully understand where to put the parentheses, I think this link can be helpfull: http://stackoverflow.com/questions/7854969/sql-multiple-join-statement.

Anonymous said...

Great Information!!!

Rajeev Verma said...

Thank you!

Doug Glancy said...

Greetings from the future. This is true in MS Query as well. Thanks a bunch!

Anonymous said...

Nice blog. Thank you :-)
Luca

Anonymous said...

8 years later, still so usefull ;-)
Many thanks,

Anonymous said...

Thanks for the info. I see all these lives you've saved and wonder why I can't experience such joy... The method won't work for me. Anyone see what I'm doing wrong?

SELECT t1.Location_ID, t2.EX_TotalHours, t3.EX_PROP_TotalHours

FROM ((HoursSpentEditingByLocation_01 AS t1)
RIGHT JOIN HoursSpentEditingByLocation_EX_Totals AS t2 ON t1.Location_ID = t2.Location_ID)
RIGHT JOIN HoursSpentEditingByLocation_EX_PROP_Totals AS t3 ON t1.Location_ID = t3.Location_ID

GROUP BY t1.Location_ID, t2.EX_TotalHours, t3.EX_PROP_TotalHours;