Larry S
asked on
Access join syntax when converting to T-SQL query
Hi,
I am recreating some Access queries in SQL Server. Exporting the Access query is easy enough. And while there are plenty of syntax differences between Access and T-SQL, I understand those differences fairly well. Except one. I've seen this in a couple of different queries now. I understand that Access uses parentheses with joins, that is not my issue. However, this query seems to have a nested join and I have questions about it.
First question. You can clearly see the first table in my From Clause, but why doesn't an "ON clause" follow immediately after? Instead, the "On Clause" seems to be the very bottom row of code I posted below.
FROM dbo.table1
INNER JOIN ((((dbo.rpt_SalesHistory
INNER JOIN [CorrectionList] ON dbo.rpt_SalesHistory.ItemC lass = [CorrectionList].ItemClass )
INNER JOIN [RegionalManagerCorrect] ON (dbo.rpt_SalesHistory.MGPS alesPerson = [RegionalManagerCorrect].M GPSalesPer son)
AND (dbo.rpt_SalesHistory.Cust omerShortN ame = [RegionalManagerCorrect].[ Business Unit Name])
AND (dbo.rpt_SalesHistory.busi ness_unit = [RegionalManagerCorrect].[ Business Unit]))
INNER JOIN [SalesGroupCorrection] ON dbo.rpt_SalesHistory.Custo merShortNa me = [SalesGroupCorrection].[Cu stomer Short Name])
INNER JOIN [PrimaryVendor] ON dbo.rpt_SalesHistory.OrrPN = [PrimaryVendor].ITEMNMBR)
ON ([PrimaryVendor].PRIMVNDR = dbo.table1.VENDORID) AND (dbo.table1.ITEMNMBR = [PrimaryVendor].ITEMNMBR)
I've tried joining these tables in SQL a number of different ways, but I cannot get anywhere near the row count that I get in access, so my I haven't joined them correctly yet. Does anyone know how to interpret this as a SQL From Clause?
I am recreating some Access queries in SQL Server. Exporting the Access query is easy enough. And while there are plenty of syntax differences between Access and T-SQL, I understand those differences fairly well. Except one. I've seen this in a couple of different queries now. I understand that Access uses parentheses with joins, that is not my issue. However, this query seems to have a nested join and I have questions about it.
First question. You can clearly see the first table in my From Clause, but why doesn't an "ON clause" follow immediately after? Instead, the "On Clause" seems to be the very bottom row of code I posted below.
FROM dbo.table1
INNER JOIN ((((dbo.rpt_SalesHistory
INNER JOIN [CorrectionList] ON dbo.rpt_SalesHistory.ItemC
INNER JOIN [RegionalManagerCorrect] ON (dbo.rpt_SalesHistory.MGPS
AND (dbo.rpt_SalesHistory.Cust
AND (dbo.rpt_SalesHistory.busi
INNER JOIN [SalesGroupCorrection] ON dbo.rpt_SalesHistory.Custo
INNER JOIN [PrimaryVendor] ON dbo.rpt_SalesHistory.OrrPN
ON ([PrimaryVendor].PRIMVNDR = dbo.table1.VENDORID) AND (dbo.table1.ITEMNMBR = [PrimaryVendor].ITEMNMBR)
I've tried joining these tables in SQL a number of different ways, but I cannot get anywhere near the row count that I get in access, so my I haven't joined them correctly yet. Does anyone know how to interpret this as a SQL From Clause?
ASKER
Hi Jim,
Thanks for responding. I actually stumbled across your article while researching possible solutions online. Great info in there!
Thanks for responding. I actually stumbled across your article while researching possible solutions online. Great info in there!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, Jim. I think this is going to work. Much appreciated. And yeah...this Access DB wasn't built by me. I have a lot of clean up to do regarding spaces in column names (and table names). Thanks so much!
Might have to add a section to this article, which would be a good read for you right now .. Migrating your Access Queries to SQL Server Transact-SQL