MYSQL Delete w/ Join

Question

CREATE TABLE `clients` (
   `client_id` 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 Client Posts
$query = "
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 posts that are posted in projects that have the passed client_id.

This is not working right now (no posts are deleted)

Answer 1

I'm more used to the subquery solution to this, but I have not tried it in MySQL:

DELETE  FROM posts
WHERE   project_id IN (
            SELECT  project_id
            FROM    projects
            WHERE   client_id = :client_id
        );