album tree deletion

Question

i have following table

album_id    |    sub_album_id    |     name
sdf2342     |    0               |   family
a2243d      |    sdf2342         |   wife
23ods       |    sdf2342         |   jack
23jskd      |    0               |   places

i want to delete selected album with its all the sub_album and if there is more sub_album then delete them too.. so even sub_album can have a sub_album.. just like folder tree view.. there is no limit.

how can i delete all the sub albums of an album with the selected album.. doing it with one query.

Answer 1

Delete from myTable 
where sub_album_id = 'sdf2342'
OR album_id = 'sdf2342'

EDIT 2/2/10: I misunderstood the unlimited hierarchy,
Try this then:

MyLoop: LOOP
    DELETE FROM MyTable
    WHERE album_ID = 'sdf2342';

    IF SELECT count(*) where sub_album_id not in (select album_ID from MyTable) = 0 THEN LEAVE MyLoop END IF

    DELETE FROM MyTable WHERE sub_album_id not in (select album_ID from MyTable)

END LOOP MyLoop

My Syntax may be a little off because I'm trying to adapt a scripting language to stand alone SQL so you might have to work it. And this is going to blow up your processor on a large table unless it's indexed properly (need an index on sub_album_id and album_id at the least)

Answer 2

If you wanting to delete rows recursively from a hierarchy, there is some good background here:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html