Using Expressions in SQL Joins (1 Viewer)

[hk1]

I'm using MS Access to connect to a MySQL database through ODBC linked tables. It appears that Access (or the ODBC driver) will not process a SQL statement that MySQL has no trouble processing. It is as follows:

SELECT recipelabel.labelid, parent1.lbltext, node.lbltext AS LabelPath
FROM 
    labels AS parent1, 
    labels AS parent2, 
    recipelabel 
    INNER JOIN labels AS node 
    ON recipelabel.labelid=node.id
    LEFT JOIN labels AS parent3 
    ON node.lft Between parent3.lft And parent3.rgt 
         And parent3.nodelevel=3
WHERE node.lft Between parent1.lft And parent1.rgt 
  And node.lft Between parent2.lft And parent2.rgt 
  And parent1.nodelevel=1 And parent2.nodelevel=2
  And recipelabel.recipeid=4

Here's the error message I get:

Syntax error (missing operator) in query expression 'recipelabel.labelid=node.id LEFT JOIN labels AS parent3 ON node.lft BETWEEN parent3.lf'

 

[hk1]

I wasn't able to get my SQL Join with the expression to work so I created a MySQL View and linked to it as though it is a table using ODBC. I consider this one solved for now.