Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-09-19
13
Medium Priority
?
233 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 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