Link to home
Start Free TrialLog in
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.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
Avatar of Argenti
Argenti
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial