Query For the Tree Structure

[Sibtain]

How do i select/query all the tree from parent to the last node includeing all levels within the one table which have parent and child fields



e.g

C_Key	Parent	Order	Description
1		1	M1
2	1	1	M1S1
3	2	1	M1S1O1
4	2	2	M1S1O2
5	1	2	M1S2
6	5	1	M1S2O1
7	5	2	M1S2O2
8		2	M2
9	8	1	M2S1
10	9	1	M2S1O1
11	9	2	M2S1O2
12	8	2	M2S2
13	12	1	M2S1O1
14	12	2	M2S1O2

Now if I want to select the first set through the SQL up to the as much as i want down level.... but i am unable to write the query to select the sepecific set of values from top to botom or parent to last chiled node.... any help will be highly appriciated






















Regards

[rpeare]

I think your example data set is incorrect. Items 13 and 14 should be M2S2O1 and M2S2O2 respectively.

I can think of a couple of ways to do this.

The first is to alter your data entry so that the parent AND the master ID are preserved.

Barring that how you perform your search depends on how you set up the queries.

Let's say you pick a tier 3 item, do you want to be able to find the entire tree of items that relate to that third tier item? If so you'd have to recursively go up your tree until you find the parent item(s)

What if you pick a tier 2 item, you'd have to look 'up' and 'down' in tiers to find parent and child items.

If however you're *always* going to pick a tier 1 item and you want to recursively go down your tiers (even if it's more than 3 tiers) the easiest thing I can think to do is to add a single field to your items table that's basically a yes/no field that says whether or not you want to include it in your query then update that yes/no flag with a series of queries then have a query that picks up anything with a 'yes' in that field.


I'd just like to say, that if you are always going to have 3 tiers, and you're always going to be looking 'down' the tree, by far the easiest thing to do would be to preserve the 'main' parent item during your data entry.

[Sibtain]

your are right that is a text only to explain ......
I think i need to put some extra light on it to explain it properly
just consider one condition
if i have a combo form with value 1 to 7 ok now if i select 1 from the combo
then all the tree of C_Key=1, here it is "M1" should be selected through query
upto the last node regardless of how deep the rabit hole is, like the following set
i need a query which can filter the following set on the basis of one value which
i user can chose on the runtime for the value 1 follwing set should be filtered for
for the value set another set should be filter and so on regardless of levels, let
suppose if I write this query
SELECT * FROM mytable WHERE Parent = 1..... then it will bring only first level
of the value 1 but i need to have 2nd,3rd, 4th .... nth Level... do u think that
i need to define the seperate views or every level.... it would be a hadic job in
this case i may need to create a view every time when i introduce new level
or do you hava any other suggesstion?

C_Key	Parent	Order	Description
1	0	1	M1
2	1	1	M1S1
3	2	1	M1S1O1
4	2	2	M1S1O2
5	1	2	M1S2
6	5	1	M1S2O1
7	5	2	M1S2O2

[rpeare]

If you are trying to find all parts associated with (both up and down from the item selected) the easiest way, as I said before, is to add another field to your table that is something like "Master" which would would the C_KEY of the highest parent record so instead of the table you have you'd have something like:

C_Key	Parent	Order	Master	Description
1	0	1	1	M1
2	1	1	1	M1S1
3	2	1	1	M1S1O1
4	2	2	1	M1S1O2
5	1	2	1	M1S2
6	5	1	1	M1S2O1
7	5	2	1	M1S2O2

Which should be easy enough to accomplish at the time of data entry.

Barring that you will have to perform the operation you're trying to do with vb script and likely a temporary table.

[June7]

It's easy enough to search down the tree. Include columns C_Key and Description in combobox and the Description value can be referenced like:

SELECT * FROM tablename WHERE InStr([Description], [comboboxname.Column(1)])>0;

[rpeare]

That was my first thought too june, but I find it hard to believe those are the actual descriptions and I think he indicated that the text he chose as the description was only for the purposes of the example.

[June7]

I have seen questions similar to this before. The Description could be a hierarchical paragraph numbering structure for some kind of reference manual.

Here is another query to consider that should search up and down:

SELECT * FROM tablename WHERE InStr([Description], [comboboxname.Column(1)])>0 OR [comboboxname.Column(1)] Like [Description] & "*";

[Dunro]

The challenge is having the query look at a variable number of levels. What I've done in the past is load the data into a tree (I originally used a Treeview control, but I now have a few custom class modules). Once the data is loaded hierarchically, I go down the tree and save the results to a temporary table.

Unfortunately, SQL in Access can't handle a variable number of levels, which is why I rely on a VBA macro to do the job for me.

[rpeare]

If part of your data entry is that you record a MASTER record along with your parent, then it doesn't matter what they choose you can select everything that has a master record of the selected item no matter where it is in your tree structure. Barring that you would have to use VBA to pull information or a series of queries, to me storing the masterID on every part is a heck of a lot easier.