Multiple LEFT JOIN in Access

Question

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 1

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.

Solution 2

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]));

Comments

[Fionnuala]

Though I have my doubts about {0} :)

[grjj3]

Oh thanks, and I see that I've missed the test4 ON part in the last join.

[Olivier Jacot-Descombes]

Probably he wants to replace it using String.Format(sql, some_id);, if he is using C# or VB.NET.

[grjj3]

Yes, it's string.Format. Doesn't really matter here anyway.