Delete all records with JOIN

I have 3 tables:

AAA with the columns:

id  |  text

BBB with the columns (aaa_id is a foreign key => id from the previous table - AAA):

id  |  text  | aaa_id

and CCC with the columns (bbb_id is a foreign key => id from the previous table - BBB):

id  |  text  | bbb_id

Now when I delete a record from the table 开发者_StackOverflowAAA I want to delete all the records from the table BBB which are aaa_id = AAA.id and also delete all the records from the table CCC which are connected to the records I'd like to delete from BBB (so they have bbb_id equal to BBB.id of the records which are aaa_id equal to AAA.id).

I've tried to user inner join but I had no luck. Meanwhile, I select all the records with PHP and delete them separately.

The tables are MyISAM.

DELETE AAA, BBB, CCC
    FROM AAA
    LEFT JOIN BBB ON BBB.aaa_id = AAA.id
    LEFT JOIN CCC ON CCC.bbb_id = BBB.id

You could use INNER JOIN as well, if there's always child records in each table. Or you can set up ON DELETE CASCADE in your foreign key constraints.

You are looking for the ON DELETE CASCADE option.

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

If you want to delete the dependent rows from another table you have to use a table engine, which supports foreign keys.

Then set the ON DELETE action to CASCADE

More documentation could be found here: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Create the foreign keys with on delete cascade that way when you delete from the parent table all child rows will be deleted

Foreign Key constraints as ON DELETE CASCADE

when u creating table follow syntax like the following example,

CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
`name` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ids` (`ids`)
CONSTRAINT `foreign` FOREIGN KEY (`ids`)
REFERENCES `table2` (`ids`) ON DELETE CASCADE ON UPDATE CASCADE
)

Note: You need to use InnoDB storage engine, he default MyISAM storage engine not support foreign keys relation.

otherwise use inner join for deletion