Solved

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

Posted on 2004-09-19
13
224 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Any Way to Print an Import Spec? 3 32
FrontEnd tools to create web database application 7 62
User Level Security 6 38
trying to catch ODBC error when database opens 2 28
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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