Help Understanding Join Syntax In Access

[zephaneas]

I don't undersand the JOIN syntaxin Access. Assuming this query:

http://www.maroisconsulting.com/temp/query.png

MS Sql does it with one join per line. How does Access come up with this strange nesting of JOINs? Can someone explain.

 
SELECT Count(Employees.EmployeeId) AS Total
FROM (((Employees INNER JOIN Titles ON Employees.TitleId = 
Titles.TitleId) INNER JOIN EmployeeStores ON Employees.EmployeeId = 
EmployeeStores.EmployeeId) INNER JOIN Stores ON 
EmployeeStores.StoreId = Stores.StoreId) INNER JOIN Groups ON 
Stores.GroupId = Groups.GroupId

Thanks

[pkstormy]

The syntax itself for MS SQL should be the same. Are you confusing that it's just on different lines? Or are you comparing a vba sql statement to a MS SQL cursor or trigger versus a view?

[zephaneas]

It's not the same at all in MS SQL.

In SQL you don't have nested Joins. I can't figure out how the designer comes up with it.

[pkstormy]

Again, are you referring to a VIEW in SQL Server or a CURSOR, STORED PROCEDURE or TRIGGER?

(I'm assuming MS SQL is also referring to SQL Server.)

It's been a while since I've designed a view, cursor, stored procedure or trigger in SQL Server. You're probably right on nested queries but the join type syntax should be the same.

[orange]

I think this syntax is valid (untested)

SELECT Count(Employees.EmployeeId) AS Total
FROM
Employees
,EmployeeStores
,Stores
,Groups
WHERE
Employees.TitleId = Titles.TitleId and
Employees.EmployeeId = EmployeeStores.EmployeeId and
EmployeeStores.StoreId = Stores.StoreId and
Stores.GroupId = Groups.GroupId

[pkstormy]

This:
SELECT Count(Employees.EmployeeId) AS Total
FROM
Employees
,EmployeeStores
,Stores
,Groups
WHERE
Employees.TitleId = Titles.TitleId and
Employees.EmployeeId = EmployeeStores.EmployeeId and
EmployeeStores.StoreId = Stores.StoreId and
Stores.GroupId = Groups.GroupId

is the same as this:
SELECT Count(Employees.EmployeeId) AS Total FROM Employees ,EmployeeStores ,Stores, Groups WHERE Employees.TitleId = Titles.TitleId and Employees.EmployeeId = EmployeeStores.EmployeeId and EmployeeStores.StoreId = Stores.StoreId and Stores.GroupId = Groups.GroupId

but this (from your 1st post):
SELECT Count(Employees.EmployeeId) AS Total
FROM (((Employees INNER JOIN Titles ON Employees.TitleId =
Titles.TitleId) INNER JOIN EmployeeStores ON Employees.EmployeeId =
EmployeeStores.EmployeeId) INNER JOIN Stores ON
EmployeeStores.StoreId = Stores.StoreId) INNER JOIN Groups ON
Stores.GroupId = Groups.GroupId

is using an INNER JOIN (ie. all records from one table and only matching records from the joined table.) If you copy and paste the 1st syntax above into the query SQL, then switch to the graphical designer mode and compare that to the last syntax pasted into a query SQL and look at the graphical designer mode, I 'think' you'll notice a difference in how the joins are put together (ie. the arrow on the join for the tables will be different.)