Delete with Join in MySQL

Question

Here is the script to create my tables:

CREATE TABLE clients (
   client_i INT(11),
   PRIMARY KEY (client_id)
);
CREATE TABLE projects (
   project_id INT(11) UNSIGNED,
   client_id INT(11) UNSIGNED,
   PRIMARY KEY (project_id)
);
CREATE TABLE posts (
   post_id INT(11) UNSIGNED,
   project_id INT(11) UNSIGNED,
   PRIMARY KEY (post_id)
);

In my PHP code, when deleting a client, I want to delete all projects posts:

DELETE 
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;

The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.

This is not working right now because no posts are deleted.

Answer 1

Or the same thing, with a slightly different (IMO friendlier) syntax:

DELETE FROM posts 
USING posts, projects 
WHERE projects.project_id = posts.project_id AND projects.client_id = :client_id;

BTW, with mysql using joins is almost always a way faster than subqueries...