Join query for two tables in MS Access (One table has recursive nature)
Posted on 2004-09-19
I need help on an sql query.
Seems little harder for me.
Here is the scenario:
I have two tables: G_Category and G_Term.
GC_ID GC_Parent GC_Category
1 0 CatA
2 0 CatB
3 0 CatC
4 1 SubCatA1
5 1 SubCatA2
6 1 SubCatA3
7 4 SubSubCatA1
8 7 SubSubSubCateA1
G_Category has some main Categories and SubCategories. Main Categories have their GC_Parent set to 0 (zero).
SubCategories are defined in terms of the GC_Parent ids (they are the GC_ID for the Categories/SubCategories.
For a given GT_TD (from G_Term), I need to find the top level GC_ID (where GC_Parent = 0) considering that each major Category has some different levels of subcategories (there are maximum of 7 levels of subcategories) and the GT_ID can be at any subcategory level.
It will also be great to know how to find the intervenign SubCategories (i.e. Intervening
GC_ID s, but I really don't need them now. :)
Please let me know how can I do that?