?
Solved

Join query for two tables in MS Access (One table has recursive nature)

Posted on 2004-09-19
13
Medium Priority
?
236 Views
Last Modified: 2008-02-01
Hi,
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.

G_Category table:
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_Term table:
GT_ID GC_ID

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?

Thanks.
_Esam
0
Comment
Question by:_Esam
  • 7
  • 6
13 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12095891
I'm not entirely clear on what you need, but if you just want to get all GT_IDs where the GC_Parent is 0, you can do this:

SELECT G_Term.* FROM G_Term INNER JOIN G_Category ON G_Term.GC_ID=G_Category.GC_ID WHERE G_Category.GC_Parent=0
0
 

Author Comment

by:_Esam
ID: 12097080
Hi,
I don't think this query does it for what I was expecting.
You can see that the G_Category has recursive nature.
There are subcategories in the G_Category table, do you see that?
Subcategories are defined in terms of the parant ids (GC_Parent)
Top level categories (wherer GC_Parent = 0) are the the main categories.
And there are subcategories for this main categories as defined in terms of GC_Parent.

Thus #4 is a subcategory of #1
         #5 is a subcategory of #1


         #7 is a subcategory of #4

For each main category (GC_parent =0) , there are arbitrary levels of subcategories.

I needed to know if GT_ID is related at any sublevel (say #7 here), how do I figure out what the main category is (where parent id is 0)?

How do I determine the top level category id if there are arbitraray levles of depth for the subcategories but a maximum of 7 levels.

I hope I am clear, please let me know.
_ESam
0
 

Author Comment

by:_Esam
ID: 12097107
>>>> GT_ID is the Glossary Term ID that can be related to any level in the category/subcategory of the G_Category table.

A term can be linked to the G_Category table at any level.
Each subcategory is bound to one top level category (parent id = 0).
I needed to know how to get the top level category ids given a GT_ID at any level of the subcategoires???

I hope I am clear..

_Esam
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12098002
Hmm ok, you'd need some kind of VBA function to do this then. It wouldn't be quick, depending on how many categories you have. Something like this:

Function GetTopLevelID(FindID As Long) As Long
Dim rst As DAO.Recordset

Set rst=CurrentDb.OpenRecordset("G_Category")
rst.FindFirst "[GC_ID]=" & FindID
While rst!GC_Parent<>0
   rst.FindFirst "[GC_ID]=" & rst!GC_Parent
Wend
GetTopLevelID=rst!GC_ID
rst.Close
Set rst=Nothing
End Function

You'd now call it in a query:
SELECT *, GetTopLevelID(G_Term.GC_ID) As TopCat FROM G_Term
0
 

Author Comment

by:_Esam
ID: 12099453
Hi,
Your example looks greate.
But unfortunately I am using VB.NET alongwith ADO.NET
I would greately appreciate if you could  please help me with the VB.NET version.
I'm sorry, I should have been clear with the language preference.
Thanks.

_Esam
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1200 total points
ID: 12099476
Sorry, I'm not familiar with either of those. Modifying the code to use ADO (not ADO.NET) is relatively straightforwards, but I don't know what else you would need to do to make it work in VB.NET. The ADO version:

Function GetTopLevelID(FindID As Long) As Long
Dim rst As ADODB.Recordset

rst.Open "G_Category", CurrentProject.Connection, adOpenStatic
rst.Find "[GC_ID]=" & FindID
While rst!GC_Parent<>0
   rst.Find "[GC_ID]=" & rst!GC_Parent
Wend
GetTopLevelID=rst!GC_ID
rst.Close
Set rst=Nothing
End Function

I think you'd probably just have to alter the CurrentProject.Connection to get this to work.
0
 

Author Comment

by:_Esam
ID: 12099494
Thanks for your explanation.
Is it not possible to do a join on the two tables ? and find the top leve GC_ID for a given GT_ID?
I was kinda hoping that it might also be possible to do it with an sql join on two tables...
Of course it will be too long?

_Esam
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099501
Since the join would be recursive, I'm afraid you can't - there is no way (in Access' SQL) to traverse a recursive tree with an undefined number of levels.
0
 

Author Comment

by:_Esam
ID: 12099533
So, strictly speaking, I will have to use a function to do that, right?
As you illustrated with one.

_Esam
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099542
Yes, that's right unfortunately.
0
 

Author Comment

by:_Esam
ID: 12099557
Thanks for your help.
I would ask a question in the asp.net (ado.net) area extracting your illustration to see if I can get an ado.net version, if that is ok with you.
Please let me know.

_Esam
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099567
That's fine by me :)
0
 

Author Comment

by:_Esam
ID: 12099575
Thanks much.
_Esam
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question