I have a set of data that models a hierarchy of categories. A root category contains a set of top-level categories. Each top-level category contains a set of sub-categories.
Each sub category has a set of organizations. A given organization can appear in multiple sub categories.
The leaf nodes of this hierarchy are organizations. An organization can potentially appear in multiple sub-categories.
The data is stored in three SQL tables:
1 Org A
2 Org B
3 Org C
4 Org D
5 Org E
6 Org F
category_id parent_id category_name
0 NULL Top Level Category
1 0 First Category
2 0 Second Category
3 1 Sub Category A
4 1 Sub Category B
5 1 Sub Category C
6 2 Sub Category D
organizations_categories -- Maps organizations to sub_categories
I would like to be able to select a list of all unique organizations under a given category or sub-category.
The way I'm doing it right now involves first figuring out which sub categories have been requested and then looping through each sub_category in code and performing a select to get all organizations mapped to that category. The results of each select are appended to an array. This array contains duplicates whenever an organization appears in multiple sub categories.
I would love to replace this kludge with a query that can efficiently select a list of distinct organizations given an id of one of the categories in the hierarchy.
I am devloping this solution using PHP and MySQL.
Thanks for your time and suggestions.
posted date: 2008-12-18 09:41:00