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
Replace `:id` by the root entry id to be deleted in the following:

```
DELETE FROM table
WHERE
   id = :id 
   OR id IN (  
-- first level
SELECT DISTINCT id sid
FROM table
WHERE parentid = :id
-- second level
UNION
SELECT DISTINCT t2.id sid
FROM table t1
INNER JOIN table t2
ON t1.id = t2.parentid
WHERE t1.parentid = :id
-- third level
UNION
SELECT DISTINCT t3.id sid
FROM table t1
INNER JOIN table t2
ON t1.id = t2.parentid
INNER JOIN table t3
ON t2.id = t3.parentid
WHERE t1.id = :id
)

```

Note that you have 3 levels, but the root node occupies one of them, which means that you actually only need to select the children and grandchildren. The query above does one more level (the 3rd one), so you may remove the part after `-- third level` down to the closing parenthesis.