1

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.

alanlittle
  • 460
  • 2
  • 12
  • Possible duplicate of [Can't do 2 joins on an MS Access query?](https://stackoverflow.com/questions/28479690/cant-do-2-joins-on-an-ms-access-query) – Andre Jun 12 '17 at 19:16
  • Which table does `TookName` belong to? Specify the alias and put another pair of parentheses around the `ON` part: `(persons P left join relatives R on (R.Person = P.PersonID and x.TookName=true))` -- see https://social.msdn.microsoft.com/Forums/office/en-US/15c36745-f7a4-4926-9687-7161e5894468/join-expression-not-supported-error-caused-by-unbracketed-join-expression-comprising-string?forum=accessdev&forum=accessdev – Andre Jun 12 '17 at 22:12
  • 1
    On an unrelated note, you might also consider keeping the MySQL backend and using Access as frontend with linked tables and/or PassThrough queries that keep using MySQL syntax. – Andre Jun 12 '17 at 22:18
  • @Andre `TookName` belongs to `relatives`. But the extra parentheses did it; that MSDN post covers it. Funny thing is, per MySQL syntax, I had them there, but took them off when I read that SO post you linked to :) Too bad there's not some consolidated reference for migrating queries; I'll probably run into more. WRT your note, I'm converting a PHP app to VB for an honors project in VB class (for which I'm required to seek outside help). I would like to stick with MySQL, and SQLite for local storage, but haven't figured those out yet, so just exploring Access to keep my options open. – alanlittle Jun 12 '17 at 22:35
  • As Andre suggested combine MySQL and Access, not a real miracle with ODBC [Using Microsoft Access as a Front-end to MySQL](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked-tables.html) and [Using Connector/ODBC with Microsoft Applications](https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft.html) I use this for years. – BitAccesser Jun 15 '17 at 01:25
  • @BitAccesser An interesting option. Thanks, I'll look into it. – alanlittle Jun 19 '17 at 12:04

2 Answers2

0

the part left join relatives R on (R.Person = P.PersonID and TookName)

seems not complete (or not a valid sql expression)

tookName is not compared with nothings

could be you need somthings like :

left join relatives R on R.Person = P.PersonID and R.TookName = P.TookName 

or

left join relatives R on R.Person = P.PersonID and R.TookName = 'FIXED VALUE'

or

left join relatives R on R.Person = P.PersonID and R.TookName is not null

for cross platform where on boolean you should use

  left join relatives R on R.Person = P.PersonID and R.TookName =1

or better WHERE your_column <> 0

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • `TookName` is a boolean, indicating whether a married person took their spouse's family name. The whole query works fine in MySQL, and the partial query (including that field) works fine in Access, it just blows up when I add the second join. – alanlittle Jun 12 '17 at 19:16
  • answer update in some platform the implicit where on boolean is not allowed so you can check for true =1 or false = 0 – ScaisEdge Jun 12 '17 at 19:18
  • and tookname = true. – xQbert Jun 12 '17 at 19:19
  • @xQbert .. thanks .. .. for the useful suggestion – ScaisEdge Jun 12 '17 at 19:19
0

The Access SQL parser is a huge fan of parentheses.

They are needed in all JOINs with more than 2 tables

FROM (a JOIN b ON a.id = b.id) JOIN c on b.id = c.id

so that only two tables / subqueries are joined in one set of parentheses.

And (as I learned today) they are needed around the ON clause if you want to use literal values in it.

FROM (a JOIN b ON (a.id = b.id AND b.foo = True)) JOIN c on b.id = c.id

An extended description is here. Link was found here: https://stackoverflow.com/a/23632282/3820271

Andre
  • 26,751
  • 7
  • 36
  • 80