Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 998
grjj3 Avatar asked Dec 20 '11 20:12

grjj3


2 Answers

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.

like image 92
Olivier Jacot-Descombes Avatar answered Sep 21 '22 20:09

Olivier Jacot-Descombes


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]));
like image 38
JeffO Avatar answered Sep 19 '22 20:09

JeffO