Question: I have a hierarchy system setup that stores the level and parent ID of the item in a database. What I am trying to figure out is how would I go about finding all the child rows of the one the user select. Here is a sample of the table structure:

```
+----+---------+----------+-------+
| Id |  label  | parentid | level |
+----+---------+----------+-------+
|  1 | honda   |        0 |     1 |
|  2 | accord  |        1 |     2 |
|  3 | civic   |        1 |     2 |
|  4 | element |        1 |     2 |
|  5 | v4      |        2 |     3 |
|  6 | v6      |        2 |     3 |
+----+---------+----------+-------+

```

So, if someone deletes `honda`, how would I go about deleting everything down the hierarchy? The database will only ever have down to a level 3.

Answer: 
Refer, **[Recursive MySQL Query with relational innoDB](https://stackoverflow.com/questions/16019105/recursive-mysql-query-with-relational-innodb/16023509#16023509)**

I had posted a answer yesterday for the same

**Update:**

Below is a `function` based on the above link, but has been tailored for your `DELETE` requirements

```
CREATE PROCEDURE `delete_relations`(`_id` INT)
BEGIN
DECLARE  delete_row_ids varchar(200);
DECLARE id, id2 varchar(200);
DECLARE rows_affected int;

SET delete_row_ids  = _id;
SET id              = _id;
SET id2             = id;

WHILE id2 IS NOT NULL DO 
    SET id = NULL;
    SELECT GROUP_CONCAT( hierarchical_data.id ), CONCAT( GROUP_CONCAT( hierarchical_data.ID ),',',delete_row_ids) INTO id, delete_row_ids FROM hierarchical_data WHERE FIND_IN_SET( parentid , id2 ) GROUP BY parentid ;
    SET id2 = id;
END WHILE;  

DELETE FROM hierarchical_data where FIND_IN_SET(hierarchical_data.id, delete_row_ids);
SELECT row_count() INTO rows_affected;

if rows_affected > 0 THEN
SELECT delete_row_ids as row_ids_deleted;
ELSE
SELECT 'NO_ROWS_DELETED';
END IF;

END//

```

[**SQLFiddle**](http://sqlfiddle.com/#!2/1bf54)

Use

```
CALL delete_relations(1)

```

To delete all entries and its relations for `id = 1 honda`

Hope its helpful
Assuming you are on MySQL 5 that has triggers you can write a small before delete trigger to achieve this easily. I have not checked the syntax exactly but will match more or less with the following:

```
CREATE TRIGGER tr_tablename_bd BEFORE DELETE ON tablename
  FOR EACH ROW BEGIN
    DELETE FROM tablename WHERE parentID = deleted.id
END;

```

The above code will also work with unlimited levels and not just 3 levels. You will need to delete a row and the trigger will delete the rows of data in the below levels or rows that mark the deleted row as parentID.

I however, vaguely remember an issue with MySQL wherein it failed to launch cascaded triggers. Meaning MySQL fails to fire a trigger due to an action on another trigger. If that bug is still there in the release of MySQL you are using (or even the latest edition) then this code will not work. However, in theory it is perfectly ok and will work on every other database that has triggers for sure. For MySQL you just need to check up if they have managed to resolve that bug yet!