SQL SYNTAX ERROR HELP !!!!!!!!!!!!!!!!!!!!!!!!!!!!........................

[lndebug]

Hi all. I have an SQL query. What is WRONG

Error I get Join expression not supported


SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON s.RoleId = m.roleId
AND s.PersonId = "251"
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder

 

[Bob Barrows]

Hi all. I have an SQL query. What is WRONG

Error I get Join expression not supported


SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON s.RoleId = m.roleId
AND s.PersonId = "251" <<<-- right here
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder

Access does not support this syntax. You will need to use a subquery:

SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN
(select * from PEOPLE_ROLES WHERE PersonId = "251")
AS s
ON s.RoleId = m.roleId
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder

 

[Michel Walsh]

Try adding parentheses to the ON clause:



SELECT * FROM SETUP_MENU INNER JOIN (SELECT menuid FROM
SETUP_MENU_ROLES AS m
INNER JOIN PEOPLE_ROLES AS s
ON (s.RoleId = m.roleId
AND s.PersonId = "251" )
WHERE MenuParent IS NULL
OR MenuParent = 0
GROUP BY menuid) AS A ON itemId = menuid
ORDER BY MenuOrder




(I have not tried in that specific context, though).




Vanderghast, Access MVP