Self relationship - Creating Categories where the Parent category & child are from the same list
This seems so simple but it seems to have me stumped. I need to create a category list to export to my ecommerce cart (for food products in my case)
I have a list of categories (eg grains, rice, barley, wheat, legumes, navy beans, pinto beans, soy beans, lentils) where all categories are in the same list.
I want to create a multi level category list where both parent and child categories come from the same category list. This is needed as this is the way my opencart ecommerce solutions works.
I see this as a Self relationship as each category can relate to itself. In this case I want the structure to be like this.
grain > rice
grain > barley
grain > wheat
legumes > navy beans
legumes > pinto beans
legumes > soy beans
legumes > lentils
This idea would be the same in an example were you had a list of staff members were some are assigned as managers and others are assigned under them (another situation I would like to use this for in my business)
How can I do this???
There are so many applications for this type of relationship. Thank you in advance for any assitance you can give.
A self join would definitely look like the way to go:
Categories::__pkCategoryID = SubCategories::_fkParentCategoryID
Categories and SubCategories would be table occurrences with the same data source table.