|
 |
access thread: Circular Query Problem
Message #1 by smahajan@l... on Mon, 26 Aug 2002 06:54:59
|
|
Hi guys
I have been woring on this strange problem from quite a sometime but am
unable to get the solution. My Problem is that I have one Table which has
Data stored in Circular manner. I have to dig out the complete chain of
Data from the Table. all I have is the starting point.. I'll show you the
example..
AreaID UnderAreaID AreaName
1 0 AREA 1
2 1 area 1 child1
3 1 area 1 child2
4 2 area 1 child1 child 1
5 3 area 1 child2 child 1
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
AreaID is the Parent Column
UnderAreaID is the ChildColumn and stores the references to it's Parent
I want to get all the Regions for Areaid 6.
The RESULT set should be:
AreaID UnderAreaID AreaName
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
Please help me write the Query
Sumeet Mahajan
Message #2 by "Leo Scott" <leoscott@c...> on Sun, 25 Aug 2002 23:06:47 -0700
|
|
Looks more like a linked list than a table. I dno't know how you would do
this with a single query. I would do it with VBA and build the linked list.
|-----Original Message-----
|From: smahajan@l... [mailto:smahajan@l...]
|Sent: Monday, August 26, 2002 6:55 AM
|To: Access
|Subject: [access] Circular Query Problem
|
|
|Hi guys
|
|I have been woring on this strange problem from quite a sometime but am
|unable to get the solution. My Problem is that I have one Table which has
|Data stored in Circular manner. I have to dig out the complete chain of
|Data from the Table. all I have is the starting point.. I'll show you the
|example..
|
|AreaID UnderAreaID AreaName
|1 0 AREA 1
|2 1 area 1 child1
|3 1 area 1 child2
|4 2 area 1 child1 child 1
|5 3 area 1 child2 child 1
|6 0 AREA 2
|7 6 area 2 child1
|8 7 area 2 child1 child 1
|9 8 area 2 child1 child 1 child 1
|
|AreaID is the Parent Column
|UnderAreaID is the ChildColumn and stores the references to it's Parent
|I want to get all the Regions for Areaid 6.
|
|The RESULT set should be:
|
|AreaID UnderAreaID AreaName
|6 0 AREA 2
|7 6 area 2 child1
|8 7 area 2 child1 child 1
|9 8 area 2 child1 child 1 child 1
|
|
|Please help me write the Query
|Sumeet Mahajan
|
|
|
|
Message #3 by "Carnley, Dave" <dcarnley@a...> on Mon, 26 Aug 2002 10:03:03 -0500
|
|
SQL as implemented in Access doesn't have anything to make this easy.
Oracle can define hierarchical structures that will allow you to work easily
with this.
The quickest thing to do would be to add a new field to your table, called
something like "MasterAreaID", it would hold the top-level ID for every
node, not just the immediate parent. This will allow you to get the entire
tree using the top node, but it won't help you get branches from the middle.
Or you can write a recursive stored procedure or VBA module... messy
-----Original Message-----
From: smahajan@l... [mailto:smahajan@l...]
Sent: Monday, August 26, 2002 1:55 AM
To: Access
Subject: [access] Circular Query Problem
Hi guys
I have been woring on this strange problem from quite a sometime but am
unable to get the solution. My Problem is that I have one Table which has
Data stored in Circular manner. I have to dig out the complete chain of
Data from the Table. all I have is the starting point.. I'll show you the
example..
AreaID UnderAreaID AreaName
1 0 AREA 1
2 1 area 1 child1
3 1 area 1 child2
4 2 area 1 child1 child 1
5 3 area 1 child2 child 1
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
AreaID is the Parent Column
UnderAreaID is the ChildColumn and stores the references to it's Parent
I want to get all the Regions for Areaid 6.
The RESULT set should be:
AreaID UnderAreaID AreaName
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
Please help me write the Query
Sumeet Mahajan
Message #4 by Beth Moffitt <BethMoffitt@i...> on Mon, 26 Aug 2002 11:08:55 -0500
|
|
Sumeet,
Create a query based on AreaID
Create another query based on UnderAreaID and AreaName
Create a third query which includes these two queries, joined on AreaID and
UnderAreaID with AreaName being your results field.
Hope this is what you were looking for.
Beth Moffitt
Developer
INI, Inc.
xxx.xxx.xxxx x110
www.iniinc.com
-----Original Message-----
From: smahajan@l... [mailto:smahajan@l...]
Sent: Monday, August 26, 2002 1:55 AM
To: Access
Subject: [access] Circular Query Problem
Hi guys
I have been woring on this strange problem from quite a sometime but am
unable to get the solution. My Problem is that I have one Table which has
Data stored in Circular manner. I have to dig out the complete chain of
Data from the Table. all I have is the starting point.. I'll show you the
example..
AreaID UnderAreaID AreaName
1 0 AREA 1
2 1 area 1 child1
3 1 area 1 child2
4 2 area 1 child1 child 1
5 3 area 1 child2 child 1
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
AreaID is the Parent Column
UnderAreaID is the ChildColumn and stores the references to it's Parent
I want to get all the Regions for Areaid 6.
The RESULT set should be:
AreaID UnderAreaID AreaName
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
Please help me write the Query
Sumeet Mahajan
Message #5 by knijn@i... on Thu, 29 Aug 2002 10:40:10
|
|
Sumeet
You can work this one out using a couple of queries in 4 layers.
In the first layer (Query1) you will have to define how deep you want to
dig into the chain. The number of children at a certain depth is no
problem, they are all taken into account. I have a table name ChainTable
similar to your example:
AreaID UnderAreaID AreaName
1 0 AREA 1
2 1 area 1 child1
3 1 area 1 child2
4 2 area 1 child1 child 1
5 3 area 1 child2 child 1
6 0 AREA 2
7 6 area 2 child1
8 7 area 2 child1 child 1
9 8 area 2 child1 child 1 child 1
10 6 area 2 child2
SQL Query1:
===========
SELECT ChainTable.*, ChainTable_1.*, ChainTable_2.*, ChainTable_3.*,
ChainTable_4.*, ChainTable_5.*
FROM ((((ChainTable LEFT JOIN ChainTable AS ChainTable_1 ON
ChainTable.AreaID = ChainTable_1.UnderAreaID) LEFT JOIN ChainTable AS
ChainTable_2 ON ChainTable_1.AreaID = ChainTable_2.UnderAreaID) LEFT JOIN
ChainTable AS ChainTable_3 ON ChainTable_2.AreaID =
ChainTable_3.UnderAreaID) LEFT JOIN ChainTable AS ChainTable_4 ON
ChainTable_3.AreaID = ChainTable_4.UnderAreaID) LEFT JOIN ChainTable AS
ChainTable_5 ON ChainTable_4.AreaID = ChainTable_5.UnderAreaID
WHERE (ChainTable.AreaID)=[param].[value];
So ChainTable is linked recursively with itself 6 times (6 chains deep)
from AreaID to UnderAreaID. [param].[value] is the value of the starting
point. Thus, Query1 already traces all chains but they are in horizontal,
so we will have to transpose them. In order to do so, you will have to
create one query for each chain you go deep, in this case 6: Query2a-
Query2f.
SQL Query2a:
============
SELECT Query1.ChainTable.AreaID, Query1.ChainTable.UnderAreaID,
Query1.ChainTable.AreaName
FROM Query1;
SQL Query2f:
============
SELECT Query1.ChainTable_5.AreaID, Query1.ChainTable_5.UnderAreaID,
Query1.ChainTable_5.AreaName
FROM Query1;
As AreaID=6 gives a chain of only 4 deep, Query2e and Query2f will not
give any result but probably you will have longer chains in your database.
After this Query3 puts the pieces together with a chained UNION.
SQL Query3:
============
SELECT * FROM Query2a UNION SELECT * FROM Query2b UNION SELECT * FROM
Query2c UNION SELECT * FROM Query2d UNION SELECT * FROM Query2e UNION
SELECT * FROM Query2f;
And Query4 just to keep the empty records out. You could incorporate
the "AreaID Is Not Null" constraint in all 6 queries Query2a-Query2f if
you would like to eliminate this one.
SQL Query4:
============
SELECT Query3.* FROM Query3 WHERE (Query3.AreaID Is Not Null);
Hope this helps you.
Arno Knijn
Servizio Elaborazione Dati
Istituto Superiore di Sanità
Viale Regina Elena, 299
00161 Rome, Italy
|
|
|
View Cart
