I have a very old project which uses MySQL, which I am considering converting part of to MS Access. I'm running into problems with some of the more complex queries, and wondered if there is a reference which details the differences between Access's SQL and MySQL. For example, I have the following query:
select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
PR.LastName as MarriedName, P.Born, LocID, PlaceName,
City, County, State, Country
from persons P
left join relatives R on (R.Person = P.PersonID and TookName)
left join persons PR on (PR.PersonID = R.Relative)
left join locations L on (L.Person = P.PersonID and L.FromDate = P.Born)
where not P.Deleted
and (P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
or P.MiddleName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
or P.Nickname in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al'))
and (P.LastName = 'Little' or PR.LastName = 'Little')
group by P.PersonID
order by P.Born desc
In Access, I can get as far as the first join
:
select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
PR.LastName as MarriedName, P.Born
from persons P
left join relatives R on (R.Person = P.PersonID and TookName)
where not P.Deleted
and P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
if I add the second join it says, Syntax error (missing operator) in query expression '(R.Person = P.PersonID and TookName) left join persons PR on (PR.PersonID = R.Relative.'
Clicking the Help button very helpfully informs me, The expression you typed is not valid for the reason indicated in the message.
Gee thanks!
But I have some other rather complex queries, so beyond solving the problem with this one, I'm looking for something that will explain the differences in general.
EDIT:
So, I changed the query according to the answer linked to:
select P.PersonID, P.FirstName, P.MiddleName, P.LastName,
PR.LastName as MarriedName, P.Born
from (persons P
left join relatives R on R.Person = P.PersonID and TookName=true)
left join persons PR on PR.PersonID = R.Relative
where not P.Deleted
and P.FirstName in ('Alan','Albert','Alfred','Allan','Allen','Alvin','Al')
It tells me JOIN expression not supported
, and highlights TookName=true
. I also tried it as TookName=1
and just TookName
. I tried removing the second JOIN
, with the first in parentheses, and it still just tells me JOIN expression not supported
.