Solved

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

Posted on 2004-09-19
13
225 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 300 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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