Almost, and sorry for the ambiguity. I have two tables, Product Group and
Product.
The product group only contains the 'Product Hierarchy' and the Product
table contains all the product information.
eg
Product Group
---------------------------------
Prdgrp_id Prdprn_id Name
1 Hardware
2 Software
3 1 Monitors
4 3 17"
5 2 Productivity Software
6 5 Office tools
7 6 Word Processors
8 6 Spreadsheets
Product
----------------------------------
Product_id Prdgrp_id Name
1 7 Msoft Word
2 8 Msoft Excel
3 6 Msoft Office
So, in the above example, if I have selected Product 2(excel) I want an SQL
statement to walk up the product group hierarchy as follows.
8 Spreadsheets
6 Office tools
5 Productivity Software
2 Software
I had tried to specify a multiple table structure, but I need unlimited
hierarchy depth, and the ability to cross link and even redesign product
levels on the fly, so the multiple table approach just won't work in this
case.
-----Original Message-----
From: Eric Fitzgerald [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 14 March 2001 10:59
To: Leonard Coonan; [EMAIL PROTECTED]
Subject: Re: Unary Relationship.
Very situational....
Let me lay out what I *think* your saying your, tell me if I'm right/wrong.
You have a table with 3 (or more, but irelevent) columns. A sample of the
data is as such:
Product | Group | Name
1 | 1 | Full product listing
2 | 1 | Computers
3 | 2 | Motherboards
4 | 2 | Monitors
5 | 3 | Asus
6 | 3 | Tyan
And let's say your in the Asus sub section of motherboards, so you want to
"step up" the ladder, and find the entire sub path your in...
Well, I personally don't see a way to do this right off, what I would end up
doing in this case is redesigning the tables. Use a multiple table
structure that links to each other, so you can use joins to gather
information about categories higher up in the heirarchy.
If I misunderstood, please explain.
----- Original Message -----
From: "Leonard Coonan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 13, 2001 3:52 PM
Subject: Unary Relationship.
> Hi, I was wondering if anyone could help me with some SQL. I have a
product
> group table that has a link to itself to allow me to build a hierarchy of
> products. Each Product has a link/relationship to its parent, so I have
>
> PRODUCT GROUP
> ----------------------------
> PRDGRP_TK Primary Key
> PRDGRP_FK Foreign key of parent product group.
>
> My question is, can I retrieve all parents up the hierarchy using one SQL
> statement. I know I can use my code, (in php in this case) to keep
calling
> an Sql statement and go up one link at a time. But it would be very handy
> to be able to simply return the entire product group structure above a
> particular product group.
>
>
> thanks
>
> len.
>
>
