Clever Geek Handbook

MySQL removes all "child" elements in a hierarchy

I have a hierarchy style MySQL database setup. There are 4 table named pages

, regions

, elements

and content

. Pages are at the top and content is at the bottom.

To simplify:

page has a column:

id

areas have columns:

id
page_id

elements have columns:

id
region_id

has columns:

id
element_id

I want to delete all child pages using only the page id .

So far, I could use a nested select statement to select the bottom content using the page id, but not select items, regions, or page.

SELECT * FROM `content` WHERE `element_id` IN (
    SELECT `id` FROM `elements` WHERE `region_id` IN (
        SELECT `id` FROM `regions` WHERE `page_id` IN (
            SELECT `id` FROM `pages` WHERE `id` = 1
        )
    )
)

Is there anyway to do this effectively? Thank.

Thanks to @Hago and @Churk, here's my final solution (mostly Churk code, little modified bit):

DELETE `content`, `elements`, `regions` FROM `content`
JOIN `elements` ON `elements`.`id` = `content`.`element_id`
JOIN `regions` ON `regions`.`id` = `elements`.`region_id` 
JOIN `pages` ON `pages`.`id` = `regions`.`page_id`
WHERE `pages`.`id` = 1
+3


source to share


2 answers 


DELETE FROM content
JOIN elements ON elements.id = content.element_id
JOIN regions ON regions.id = elements.region_id 
JOIN pages ON pages.id = regions.page_id
WHERE pages.id = 1


+3


source


Inner join can be more efficient than multiple nested subqueries, try



select 
    c.id 
from 
    contents as c 
inner join 
    elements as e on c.element_id = e.id 
inner join 
    regions as r on e.region_id = r.id 
where 
    r.page_id = 1
0


source






More articles:


All Articles