Avatar of meteorelec
meteorelecFlag for Ireland asked on

MySQL query - dealing with parent- child relations in a table (multiple levels)

Hi,

I have table [categories] - this table contains all the categories in my product catalogue.

there table has a number of fields - the 2 of importance here are the categories id (Unique)
and the parent_id

The top level categories have a blank parent - this is because they are not contained inside another category , there is only 12 of these , the remaining 5,000 categories have a parent id,

The parent id on a record say "This category(category_id) is contained inside this category(parent_id) "

What i am looking for is a well optimised query that will return all the category_id's that are inside a given category id

i.e. child categories, child child categories , child child child categories - an so on.
MySQL ServerSQL

Avatar of undefined
Last Comment
Argenti

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

for hierarchical data (in MySQL), I found this article most helpful:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

you will notice that with "usual" table design, mysql lacking recursive queries, you cannot do it in 1 single query. the workaround would be to solve this with a stored procedure (for example) looping until all child records have been found...
ASKER CERTIFIED SOLUTION
Argenti

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes