[SOLVED] Multiple LEFT JOIN in Access

Multiple LEFT JOIN in Access

I have the following query, which works for MySQL:

DELETE `test1`, `test2`, `test3`, `test4` FROM
`test1` LEFT JOIN `test2` ON test2.qid = test1.id
LEFT JOIN test3 ON test3.tid = test2.id
LEFT JOIN test4.qid = test1.id
WHERE test1.id = {0}

But it doesn't work for MS Access. I've tried to add parentheses around the LEFT JOIN, but it gives me syntax error in FROM clause. So how should this query look in order to work in MS Access?

Solution

The Access DELETE requires a star (*): DELETE * FROM ...

In addition, the joins must be nested by using parentheses:

DELETE test1.*, test2.*, test3.*, test4.*
FROM
    (
      (
        test1 
        LEFT JOIN test2 ON test1.qid = test2.id
      )
      LEFT JOIN test3 ON test2.tid = test3.id
    )
    LEFT JOIN test4 ON test1.qid = test4.id
WHERE test1.id = {0}

This is specific to Access (Jet) SQL.