Sqlite delete all connected rows from 3 join table

Question

How do you delete all rows connected from 2 different table in SQLite?

**Table1**         **Table2**                     **Table3**         
| ID | Number |    | ID | Tax | Table1ID |    | ID | Price | Table2ID |
| 1  |   0    |    | 1  | 21  |     1    |    | 1  |   56  |    1     |
| 2  |   1    |    | 2  | 15  |     2    |    | 2  |   5   |    2     |
| 3  |   0    |    | 3  | 10  |     3    |    | 3  |   98  |    3     |

I want to delete all rows from Table1-3 where Table1.Number = 0, how I can get that?

Answer 1

What you need is to define the tables properly so that Table2.Table1ID references Table1.ID and Table3.Table2ID references Table2.ID with the action ON DELETE CASCADE:

PRAGMA foreign_keys = ON;

CREATE TABLE Table1(
    `ID` INTEGER PRIMARY KEY, 
    `Number` INTEGER
);

CREATE TABLE Table2(
  `ID` INTEGER PRIMARY KEY,
  `Tax` INTEGER,
  `Table1ID` INTEGER REFERENCES Table1(ID) ON DELETE CASCADE
);

CREATE TABLE Table3(
  `ID` INTEGER PRIMARY KEY,
  `Price` INTEGER,
  `Table2ID` INTEGER REFERENCES Table2(ID) ON DELETE CASCADE
);

Note that you must turn on the foreign key support because it is off by default.

Now every time you delete a row from Table1, all rows of Table2 that hold a reference to the deleted row of Table1 will be deleted too.
Also all rows of Table3 that hold a reference to the deleted rows of Table2 will be deleted.

So all you need is:

DELETE FROM Table1 WHERE Number = 0;

See the demo.