CODEWITHSUNDEEP
sqlms-accesssql-deletejet
grjj3
grjj3

Reputation: 381

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?

Upvotes: 6

Views: 43860

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112344

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.

Upvotes: 14

JeffO
JeffO

Reputation: 8043

Here is a sample select statement on three tables with left joins: 

SELECT 
FROM (Table1 LEFT JOIN Table2 ON Table1.field1 = Table2.field2) 
LEFT JOIN Table3 ON Table2.field2 = Table3.field3;

Your deleted statement:

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) = [SomeParameter]));

Upvotes: 3

Related Questions