How can I delete from two tables

Question

I have two tables employees and salary and I have prepared my query in such a way that once staff is deleted from the employees table, the staff salary is automatically deleted in the salary table.

How can I delete staff records from salary table once staff records are deleted from employees table?

Here is my SQL CODE:

$db->query('
 DELETE employees
      , salary 
   FROM employees 
 JOIN salary
  WHERE employees.id = salary.staffId 
    AND salary.staffId = :id'
 );

Answer 1

You have to left join and to change the where clause too

$db->query('
 DELETE employees
      , salary 
   FROM employees 
 LEFT JOIN salary ON employees.id = salary.staffId 
  WHERE employees.id = :id'
 );

CREATE TABLE employees(id INT) 

INSERT INTO employees VALUES(1),(2),(3),(4)

CREATE TABLE salary (id INT,staffId INT) 

INSERT INTO salary  VALUES(1,1),(2,2),(3,3)

DELETE employees
      , salary 
   FROM employees LEFT JOIN salary ON employees.id = salary.staffId 
  WHERE employees.id = 4

SELECT * FROM employees

| id |
| -: |
|  1 |
|  2 |
|  3 |

db<>fiddle here