SQL Syntax Part III

I'm back again! I can't figure this one out for the life of me. I need to convert the FROM statement from Access to SQL. The more tables there are, the more confusing it is for me. Any help would be greatly appreciated. This is the FROM statement in Access.....

FROM ((dbo_somast INNER JOIN qrySOITEM ON dbo_somast.fsono = qrySOITEM.fsono) INNER JOIN qrySORELS ON (qrySOITEM.finumberA = qrySORELS.finumberA) AND (qrySOITEM.fsono = qrySORELS.fsono)) LEFT JOIN (qryJOITEM LEFT JOIN dbo_jodbom ON qryJOITEM.fjobno = dbo_jodbom.fjobno) ON (qrySORELS.fsono = qryJOITEM.fsono) AND (qrySORELS.finumberA = qryJOITEM.finumberA) AND (qrySORELS.frelease = qryJOITEM.fkey)

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Gott a love it how Access throws in a wompload of parentheses marks.
Cleaned up, and used table aliases to make it more readable
SELECT blah, blah, blah 
FROM dbo_somast s
   JOIN qrySOITEM soi ON s.fsono = soi.fsono
   JOIN qrySORELS sor ON soi.finumberA = sor.finumberA AND soi.fsono = sor.fsono
   LEFT JOIN qryJOITEM joi ON sor.fsono = joi.fsono AND sor.finumberA = joi.finumberA AND sor.frelease = joi.fkey
   LEFT JOIN dbo_jodbom jod ON joi.fjobno = jod.fjobno 

Open in new window


This may help, although it doesn't speak to cleaning up JOINs:  Migrating your Access queries to SQL Server

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Too many parenthesis. Try this version:
FROM dbo_somast 
	INNER JOIN qrySOITEM ON dbo_somast.fsono = qrySOITEM.fsono
	INNER JOIN qrySORELS ON qrySOITEM.finumberA = qrySORELS.finumberA AND qrySOITEM.fsono = qrySORELS.fsono
	LEFT JOIN qryJOITEM 
		LEFT JOIN dbo_jodbom ON qryJOITEM.fjobno = dbo_jodbom.fjobno
		ON qrySORELS.fsono = qryJOITEM.fsono AND qrySORELS.finumberA = qryJOITEM.finumberA AND qrySORELS.frelease = qryJOITEM.fkey

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry Jim, only after submit I saw that you've answered already.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you both for your quick responses. I already printed that article you mentioned, Jim. I'm reading through it and learning as I go. Just certain parts that I still struggle with and the FROM statements is like a thorn in my side when there's multiple tables. Thank you again for your guidance!
Jim HornMicrosoft SQL Server Data DudeCommented:
Do me a favor and send me a message with the areas were the article doesn't cover what you have to learn, and I'll make a revision to the article addressing them.  

I need to go in there anyways as an EE upgrade about six months ago affected the image rendering in articles, and looks like I need to make some cosmetic changes.

Thanks for the grade, good luck with your project.  -Jim
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Will do Jim. I will message you in a few.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.