Probably an easy one...

[greg@parkvilledesigns.com]

I'm sure this is easy - if someone could give me a push in the right
direction I would apprieciate it!

If you have multiple categories

CatID - NAME - ParentID
1 root 0
2 fruit 0
3 veg 0
4 apple 2
5 orange 2
6 corn 3
7 peas 3
8 navel 5
9 sunkist 5
10 sweet 6

What type of query do I use to find out the root category of "navel"
(catID 8) - I know that it's 2 but I'm not sure how the code works...

Any help on this would be greatly apprieciated!!! !

[David Seeto via AccessMonster.com]

Re: Probably an easy one...

I'm not sure if queries are your best solution, since you would have to add
a new query every time you another level of categories. It's the sort of
thing that a routine using recursion would do very well with.

But if you do need to use queries, you need ones that look like:
SELECT Child.CatID, Parent.ParentID
FROM CatTbl as Child
LEFT JOIN CatTbl as Parent
ON Child.ParentID = Parent.CatID

To get the next set of parents, you then need to use this query and join it
up again, and so on. It gets a bit hairy from there - you start needing to
maybe put the results together using UNION statements and then limiting the
output based on that. And even if you get this done, every time you add
another hierarchy in this table, you need to add another query and modify
your UNION query...

Which is why I said up fron that, although it's possible, it's tricky, and
you're better off doing it in code...

--
Message posted via http://www.accessmonster.com

[Alan Webb]

Re: Probably an easy one...

Greg,
Access/Jet SQL has no explicit support for querying and displaying
hierarchical data. The problem you describe is a variation on the
bill-of-materials or geneology problem. Both Oracle and SQL Server, and
perhaps MSDE have techniques within the scripting languages for presenting
hierarchical data. My copy of "The Guru's guide to TransactSQL" uses a
While/Wend loop to post records to a temporary table for each level of the
hierarchy. I bought the book in 2000 when Windows NT 4 was still king of
the hill. Since then SQL Server has been through a version or two and there
are probably newer techniques for working with hierarchical data.
If possible, I'd build my database in something other than Access because
Jet's support for working with hierarchies is too limited. If Access is the
only choice, VBA will have to be used to add the scripting support you will
need in combination with SQL to get the result you want.

--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS
<greg@parkville designs.com> wrote in message
news:1114660942 .276811.35360@z 14g2000cwz.goog legroups.com...[color=blue]
> I'm sure this is easy - if someone could give me a push in the right
> direction I would apprieciate it!
>
> If you have multiple categories
>
> CatID - NAME - ParentID
> 1 root 0
> 2 fruit 0
> 3 veg 0
> 4 apple 2
> 5 orange 2
> 6 corn 3
> 7 peas 3
> 8 navel 5
> 9 sunkist 5
> 10 sweet 6
>
> What type of query do I use to find out the root category of "navel"
> (catID 8) - I know that it's 2 but I'm not sure how the code works...
>
> Any help on this would be greatly apprieciated!!! !
>[/color]

[Tim Marshall]

Re: Probably an easy one...

Alan Webb wrote:
[color=blue]
> Both Oracle and SQL Server, and
> perhaps MSDE have techniques within the scripting languages for presenting
> hierarchical data.[/color]

I don't work with MS SQL, but Oracle has the wonderful "Start
With...Connect by" as an SQL statement which displays the parent and
children and includes the "level" to which a child belongs, including
the direct parentage, the parent being 1.

Colleagues who work with the same maintenance management system I do,
but with an MS SQL back end (our back end is Oracle) tell me MS SQL does
not have the equivalent SQL. Is that incorrect? Or are the techniques
you mention more to do with stored procedures?

For an example of the Oracle SQL, see below. Boiler 1, with tag
UA1001-BLR-01 is the parent. The level numbers show the many and varied
different lines of parent-childrelationsh ips. In the table
F_Equipment, FM_TAG_NO is the actual equipment tag number and
FM_MAJOR_TAG is the parent. Yes I know this is stupid, the designers
made a own rod for their back by not using primary and foreign keys, but
their reaction for my observation on this is typical of DB designers who
have done things a bad way - "normalizat ion is good in theory but not
practical in practice" - the biggest load of horse pucky I've heard, but
I'm sure many of you have heard this sort of thing. Anyway, I digress -
there's the example:

SELECT
LEVEL,
(select FU_UNIT_ID from tma.f_areas where fu_pk = fm_fu_fk) "Area ID" ,
FM_TAG_NO "Eqpt ID",
substr(FM_DESCR IPTION,1, 20) "Descriptio n"
from
f_equipment
start with fm_tag_no = 'UA1001-BLR-01'
connect by prior fm_tag_no = fm_major_tag

LEVEL Area ID Eqpt ID Description
1 UA-1001 UA1001-BLR-01 Steam Boiler #1
2 UA-1001 UA1001-AIR-01 Air System Component
3 UA-1001 UA1001-CTRL-09 Damper Control - For
3 UA-1001 UA1001-FAN-01 Forced Draft Fan (fo
4 UA-1001 UA1001-MTR-26 Motor - Forced Draft
3 UA-1001 UA1001-WBOX-01 Wind Box (for UA1001
2 UA-1001 UA1001-EXH-01 Exhaust System Compo
3 UA-1001 UA1001-CTRL-10 Damper Control - Out
3 UA-1001 UA1001-SENR-07 Oxygen Sensor (for U
3 UA-1001 UA1001-SOOT-02 Soot Blower for #1 (
3 UA-1001 UA1001-SENR-08 Smoke Density Sensor
3 UA-1001 UA1001-SENR-09 Furnace Pressure Sen
3 UA-1001 UA1001-SOOT-01 Soot Blower for #1 (
2 UA-1001 UA1001-FUEL-01 Fuel System Componen
3 UA-1001 UA1001-VLVE-43 Control Valve - Fuel
3 UA-1001 UA1001-VLVE-45 Fuel Solenoid Valve
3 UA-1001 UA1001-METR-03 Bunker Fuel Meter (f
3 UA-1001 UA1001-VLVE-44 Fuel Solenoid Valve
2 UA-1001 UA1001-HTHW-01 HTHW System Componen
3 UA-1001 UA1001-HEX-01 Heat Exchanger #1 (H
4 UA-1001 UA1001-VLVE-42 Control Valve - HTHW
4 UA-1001 UA1001-PUMP-01 Primary Pump #1 (for
5 UA-1001 UA1001-POTC-06 Pot Cooler - Primary
5 UA-1001 UA1001-MTR-01 Motor - Primary Pump
4 UA-1001 UA1001-VLVE-41 Safety Valve - HTHW
2 UA-1001 UA1001-STM-01 Steam System Compone
3 UA-1001 UA1001-POTC-05 Pot Cooler - Conduct
3 UA-1001 UA1001-VLVE-46 Pressure Reducing Va
3 UA-1001 UA1001-VLVE-39 Safety Valve - Steam
3 UA-1001 UA1001-VLVE-40 Safety Valve - Steam
2 UA-1001 UA1001-WATR-01 Water System Compone
3 UA-1001 UA1001-VLVE-38 Control Valve - Feed
3 UA-1001 UA1001-CTRL-11 Controller - Feedwat
3 UA-1001 UA1001-WCOL-01 Water Column (for UA
3 UA-1002 UA1002-TANK-02 Chemical Tank (for U
4 UA-1002 UA1002-MTR-18 Agitator Motor (for
4 UA-1002 UA1002-PUMP-15 Chemical Pump (for U
5 UA-1002 UA1002-MTR-01 Motor - Chemical Pum
2 UA-1001 UA1001-PANL-01 Boiler Control Panel

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Alan Webb]

Re: Probably an easy one...

Tim,
The book I have uses some documented TransactSQL with a While/Wend loop to
enumerate each level and to do the other needed leg work. I recently
installed PostgreSQL on my server to see if I liked it better and so far, I
do. I haven't explored PostgreSQL and what support there is for presenting
hierarchical data but I hope it's better than what I am used to.

--
Alan Webb
knoNOgeek@SPAMh otmail.com
"It's not IT, it's IS"

"Tim Marshall" <TIMMY!@PurpleP andaChasers.Moe rtherium> wrote in message
news:d4r9e6$31m $1@coranto.ucs. mun.ca...[color=blue]
> Alan Webb wrote:
>[color=green]
>> Both Oracle and SQL Server, and perhaps MSDE have techniques within the
>> scripting languages for presenting hierarchical data.[/color]
>
> I don't work with MS SQL, but Oracle has the wonderful "Start
> With...Connect by" as an SQL statement which displays the parent and
> children and includes the "level" to which a child belongs, including the
> direct parentage, the parent being 1.
>
> Colleagues who work with the same maintenance management system I do, but
> with an MS SQL back end (our back end is Oracle) tell me MS SQL does not
> have the equivalent SQL. Is that incorrect? Or are the techniques you
> mention more to do with stored procedures?
>
> For an example of the Oracle SQL, see below. Boiler 1, with tag
> UA1001-BLR-01 is the parent. The level numbers show the many and varied
> different lines of parent-childrelationsh ips. In the table F_Equipment,
> FM_TAG_NO is the actual equipment tag number and FM_MAJOR_TAG is the
> parent. Yes I know this is stupid, the designers made a own rod for their
> back by not using primary and foreign keys, but their reaction for my
> observation on this is typical of DB designers who have done things a bad
> way - "normalizat ion is good in theory but not practical in practice" -
> the biggest load of horse pucky I've heard, but I'm sure many of you have
> heard this sort of thing. Anyway, I digress - there's the example:
>
> SELECT
> LEVEL,
> (select FU_UNIT_ID from tma.f_areas where fu_pk = fm_fu_fk) "Area ID" ,
> FM_TAG_NO "Eqpt ID",
> substr(FM_DESCR IPTION,1, 20) "Descriptio n"
> from
> f_equipment
> start with fm_tag_no = 'UA1001-BLR-01'
> connect by prior fm_tag_no = fm_major_tag
>
> LEVEL Area ID Eqpt ID Description
> 1 UA-1001 UA1001-BLR-01 Steam Boiler #1
> 2 UA-1001 UA1001-AIR-01 Air System Component
> 3 UA-1001 UA1001-CTRL-09 Damper Control - For
> 3 UA-1001 UA1001-FAN-01 Forced Draft Fan (fo
> 4 UA-1001 UA1001-MTR-26 Motor - Forced Draft
> 3 UA-1001 UA1001-WBOX-01 Wind Box (for UA1001
> 2 UA-1001 UA1001-EXH-01 Exhaust System Compo
> 3 UA-1001 UA1001-CTRL-10 Damper Control - Out
> 3 UA-1001 UA1001-SENR-07 Oxygen Sensor (for U
> 3 UA-1001 UA1001-SOOT-02 Soot Blower for #1 (
> 3 UA-1001 UA1001-SENR-08 Smoke Density Sensor
> 3 UA-1001 UA1001-SENR-09 Furnace Pressure Sen
> 3 UA-1001 UA1001-SOOT-01 Soot Blower for #1 (
> 2 UA-1001 UA1001-FUEL-01 Fuel System Componen
> 3 UA-1001 UA1001-VLVE-43 Control Valve - Fuel
> 3 UA-1001 UA1001-VLVE-45 Fuel Solenoid Valve
> 3 UA-1001 UA1001-METR-03 Bunker Fuel Meter (f
> 3 UA-1001 UA1001-VLVE-44 Fuel Solenoid Valve
> 2 UA-1001 UA1001-HTHW-01 HTHW System Componen
> 3 UA-1001 UA1001-HEX-01 Heat Exchanger #1 (H
> 4 UA-1001 UA1001-VLVE-42 Control Valve - HTHW
> 4 UA-1001 UA1001-PUMP-01 Primary Pump #1 (for
> 5 UA-1001 UA1001-POTC-06 Pot Cooler - Primary
> 5 UA-1001 UA1001-MTR-01 Motor - Primary Pump
> 4 UA-1001 UA1001-VLVE-41 Safety Valve - HTHW
> 2 UA-1001 UA1001-STM-01 Steam System Compone
> 3 UA-1001 UA1001-POTC-05 Pot Cooler - Conduct
> 3 UA-1001 UA1001-VLVE-46 Pressure Reducing Va
> 3 UA-1001 UA1001-VLVE-39 Safety Valve - Steam
> 3 UA-1001 UA1001-VLVE-40 Safety Valve - Steam
> 2 UA-1001 UA1001-WATR-01 Water System Compone
> 3 UA-1001 UA1001-VLVE-38 Control Valve - Feed
> 3 UA-1001 UA1001-CTRL-11 Controller - Feedwat
> 3 UA-1001 UA1001-WCOL-01 Water Column (for UA
> 3 UA-1002 UA1002-TANK-02 Chemical Tank (for U
> 4 UA-1002 UA1002-MTR-18 Agitator Motor (for
> 4 UA-1002 UA1002-PUMP-15 Chemical Pump (for U
> 5 UA-1002 UA1002-MTR-01 Motor - Chemical Pum
> 2 UA-1001 UA1001-PANL-01 Boiler Control Panel
>
> --
> Tim http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me[/color]

[pietlinden@hotmail.com]

Re: Probably an easy one...

I would swear Joe Celko answered a question like this a while ago.
Something about walking hierarchies or something like that... (Sorry,
it was several years ago...)

ho ho ho... I found it... well, one of them.

http://groups-beta.google.com/group/comp.databases.ms-access/browse_frm/thread/fff2a083a147b726/9111e0f8ea79936d?q=Celko+%2B+%22nested+sets%22&rnum=6&hl=en#9111e0f8ea79936d