Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-09-19
13
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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