1

I have a categories table with id, parent and name fields. The parent field allows a category to be a subcategory of another category.

Example categories table where there are two main categories (WIDGETS and THINGAMABOBS), and WIDGETS have 3 subcategories:

  • id 1, parent null, name "WIDGETS"
  • id 2, parent 1, name "GADGETS"
  • id 3, parent 1, name "DOOHICKEYS"
  • id 4, parent 1, name "GIZMOS"
  • id 5, parent null, name "THINGAMABOBS

I have a products table with category field

Example products record where product is linked to the "GIZMOS" category:

  • id 1, category 4, name Contraption 5000

I want to be able to supply a category name in a SELECT statement and get back all products that are in that category. But not only do I want to find the above record on "GIZMOS", but I also want to be able to find it by the name "WIDGET", because MEDIUM WIDGET is a child of WIDGET. This should work across an unlimted number of levels (ie: sub-sub-sub categories)

To make this even more complicated, I want to be able to assign a product to more than one category. Perhaps they would be separated by commas? i.e.: If I wanted the Contraption 5000 to exist in the Doohickeys and Thingamabobs categories, I would put 3,5 in the category field.

Is what I'm asking possible with a single select statement?

Jason Wood
  • 351
  • 1
  • 5
  • 13
  • 3
    Do not assign a product to multiple categories by forming comma delimited strings. Make a proper lookup table: **ProductCategory** *ProductCategoryID* *ProductID* *CategoryID*. It will save you a lot of pain. – Esoteric Screen Name Nov 29 '12 at 21:09

2 Answers2

1

Do this in your application code! It will be much simpler and easier to maintain.

See also this similar post (actually there are many posts on this topic)

Community
  • 1
  • 1
dwurf
  • 12,393
  • 6
  • 30
  • 42
  • Not only PostgreSQL but nearly all DBMS have the ability to do recursive queries (Oracle, SQL Server, Firebird, H2, HSQLDB, Cubrid, Teradata, Sybase, ...) –  Nov 29 '12 at 21:08
1

I'm going to start at the end of your question:

To make this even more complicated, I want to be able to assign a product to more than one category. Perhaps they would be separated by commas? i.e.: If I wanted the Contraption 5000 to exist in the Doohickeys and Thingamabobs categories, I would put 3,5 in the category field.

By doing this you are creating a many-to-many relationship, in which case you'll need a third table called products_categories that holds two columns: product_id and category_id; you'd remove the category column from your products table.

If you wanted a product with id=1 to belong to categories 3 and 5, you'd create two rows in products_categories:

product_id | category_id
------------------------
1          | 3
1          | 5

Now to the first part of your question...

What you'd need to do is create a recursive query, which I know can be done in SQL Server but honestly I'm not sure can be done in MySQL. If it can be, I'm sure someone else will come up with an appropriate answer for you.

Brian Driscoll
  • 19,373
  • 3
  • 46
  • 65
  • Combine this one with @dwurf's answer (which addresses the hierarchical data management in MySQL issue), and we'd have a full answer to the question. :) – Shauna Nov 29 '12 at 21:21