Linking of several tables in a heirarchical form

[Jacob]

I have a table, SYS_Locations, which lists all of the locations available
for a system.
I.E.:
1 -- Matrix Hall
2 -- Upstairs
3 -- Basement
4 -- Cabinet
5 -- Storage Building

Then, I have another table, LOC_TREE_REL which ties the previous locations
to one another.
I.E.:
1 -- 2 (Says Upstairs belongs in Matrix Hall)
5 -- 3 (Says Basement belongs in Storage Building)
3 -- 4 (Says the Cabinet is in the Basement which as shown in a previous
row, is in the Storage Building.)
All of the entries in LOC_TREE_REL include a self-ownership entry. (i.e. For
Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry, etc.

I need to be able to tie these locations together in some sort of list which
demonstrates heirarchy.

I'm considering using a drop-down select menu to do it such as:

Matrix Hall
---- Upstairs
Storage Building
---- Basement
---------Cabinet

However, I need some help with the code to perform such location connections
on-the-fly. Any help will be greatly appreciated.

Jacob

[Jacob]

I have a table, SYS_Locations, which lists all of the locations available
for a system.
I.E.:
1 -- Matrix Hall
2 -- Upstairs
3 -- Basement
4 -- Cabinet
5 -- Storage Building

Then, I have another table, LOC_TREE_REL which ties the previous locations
to one another.
I.E.:
1 -- 2 (Says Upstairs belongs in Matrix Hall)
5 -- 3 (Says Basement belongs in Storage Building)
3 -- 4 (Says the Cabinet is in the Basement which as shown in a previous
row, is in the Storage Building.)
All of the entries in LOC_TREE_REL include a self-ownership entry. (i.e. For
Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry, etc.

I need to be able to tie these locations together in some sort of list which
demonstrates heirarchy.

I'm considering using a drop-down select menu to do it such as:

Matrix Hall
---- Upstairs
Storage Building
---- Basement
---------Cabinet

However, I need some help with the code to perform such location connections
on-the-fly. Any help will be greatly appreciated.

Jacob

[Aaron Bertrand [MVP]]

Could you tell us what database and version you're using (SQL Server 7.0,
Access 95, FoxPro 2.0, dBase IV, Oracle 8i, etc.)?

And please follow up only in microsoft.public.inetserver.asp.db



"Jacob" <yak2016@comcast.net> wrote in message
news:O026FS1PDHA.1148@TK2MSFTNGP11.phx.gbl...

> I have a table, SYS_Locations, which lists all of the locations available
> for a system.
> I.E.:
> 1 -- Matrix Hall
> 2 -- Upstairs
> 3 -- Basement
> 4 -- Cabinet
> 5 -- Storage Building
>
> Then, I have another table, LOC_TREE_REL which ties the previous locations
> to one another.
> I.E.:
> 1 -- 2 (Says Upstairs belongs in Matrix Hall)
> 5 -- 3 (Says Basement belongs in Storage Building)
> 3 -- 4 (Says the Cabinet is in the Basement which as shown in a previous
> row, is in the Storage Building.)
> All of the entries in LOC_TREE_REL include a self-ownership entry. (i.e.

For

> Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry, etc.
>
> I need to be able to tie these locations together in some sort of list

which

> demonstrates heirarchy.
>
> I'm considering using a drop-down select menu to do it such as:
>
> Matrix Hall
> ---- Upstairs
> Storage Building
> ---- Basement
> ---------Cabinet
>
> However, I need some help with the code to perform such location

connections

> on-the-fly. Any help will be greatly appreciated.
>
> Jacob
>
>
>

[Aaron Bertrand [MVP]]

Could you tell us what database and version you're using (SQL Server 7.0,
Access 95, FoxPro 2.0, dBase IV, Oracle 8i, etc.)?

And please follow up only in microsoft.public.inetserver.asp.db



"Jacob" <yak2016@comcast.net> wrote in message
news:O026FS1PDHA.1148@TK2MSFTNGP11.phx.gbl...

> I have a table, SYS_Locations, which lists all of the locations available
> for a system.
> I.E.:
> 1 -- Matrix Hall
> 2 -- Upstairs
> 3 -- Basement
> 4 -- Cabinet
> 5 -- Storage Building
>
> Then, I have another table, LOC_TREE_REL which ties the previous locations
> to one another.
> I.E.:
> 1 -- 2 (Says Upstairs belongs in Matrix Hall)
> 5 -- 3 (Says Basement belongs in Storage Building)
> 3 -- 4 (Says the Cabinet is in the Basement which as shown in a previous
> row, is in the Storage Building.)
> All of the entries in LOC_TREE_REL include a self-ownership entry. (i.e.

For

> Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry, etc.
>
> I need to be able to tie these locations together in some sort of list

which

> demonstrates heirarchy.
>
> I'm considering using a drop-down select menu to do it such as:
>
> Matrix Hall
> ---- Upstairs
> Storage Building
> ---- Basement
> ---------Cabinet
>
> However, I need some help with the code to perform such location

connections

> on-the-fly. Any help will be greatly appreciated.
>
> Jacob
>
>
>

[Jacob]

SQL Server 7.0
Connect String is: "driver={SQL
Server};server=localhost;uid=username;pwd=password ;database=dbname;"

Jacob

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OSjTp11PDHA.2852@tk2msftngp13.phx.gbl...

> Could you tell us what database and version you're using (SQL Server 7.0,
> Access 95, FoxPro 2.0, dBase IV, Oracle 8i, etc.)?
>
> And please follow up only in microsoft.public.inetserver.asp.db
>
>
>
> "Jacob" <yak2016@comcast.net> wrote in message
> news:O026FS1PDHA.1148@TK2MSFTNGP11.phx.gbl...

> > I have a table, SYS_Locations, which lists all of the locations

available

> > for a system.
> > I.E.:
> > 1 -- Matrix Hall
> > 2 -- Upstairs
> > 3 -- Basement
> > 4 -- Cabinet
> > 5 -- Storage Building
> >
> > Then, I have another table, LOC_TREE_REL which ties the previous

locations

> > to one another.
> > I.E.:
> > 1 -- 2 (Says Upstairs belongs in Matrix Hall)
> > 5 -- 3 (Says Basement belongs in Storage Building)
> > 3 -- 4 (Says the Cabinet is in the Basement which as shown in a

previous

> > row, is in the Storage Building.)
> > All of the entries in LOC_TREE_REL include a self-ownership entry. (i.e.

> For

> > Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry, etc.
> >
> > I need to be able to tie these locations together in some sort of list

> which

> > demonstrates heirarchy.
> >
> > I'm considering using a drop-down select menu to do it such as:
> >
> > Matrix Hall
> > ---- Upstairs
> > Storage Building
> > ---- Basement
> > ---------Cabinet
> >
> > However, I need some help with the code to perform such location

> connections

> > on-the-fly. Any help will be greatly appreciated.
> >
> > Jacob
> >
> >
> >

>
>

[Aaron Bertrand [MVP]]

Might I suggest a slightly simpler schema? This makes your query *much*
simpler (avoiding nesting, self-joins, or looping), removes a table from the
world, and avoids the need to store self-ownership rows.



SET NOCOUNT ON

CREATE TABLE SYS_Locations
(
locid INT,
description VARCHAR(32),
layer INT,
parentid INT,
topid INT
)

INSERT SYS_Locations VALUES(1, 'Matrix Hall', 1, 1, 1)
INSERT SYS_Locations VALUES(2, 'Upstairs', 2, 1, 1)
INSERT SYS_Locations VALUES(3, 'Basement', 2, 5, 5)
INSERT SYS_Locations VALUES(4, 'Cabinet', 3, 3, 5)
INSERT SYS_Locations VALUES(5, 'Storage Building', 1, 5, 5)

GO

-- and look how simple this query becomes!!!
SELECT heirarchy = REPLICATE('--', layer-1)
+ ' ' + description FROM SYS_Locations
ORDER BY topid, layer

GO

DROP TABLE SYS_Locations




"Jacob" <yak2016@comcast.net> wrote in message
news:OhJfeF2PDHA.3192@tk2msftngp13.phx.gbl...

> SQL Server 7.0
> Connect String is: "driver={SQL
> Server};server=localhost;uid=username;pwd=password ;database=dbname;"
>
> Jacob
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> news:OSjTp11PDHA.2852@tk2msftngp13.phx.gbl...

> > Could you tell us what database and version you're using (SQL Server

7.0,

> > Access 95, FoxPro 2.0, dBase IV, Oracle 8i, etc.)?
> >
> > And please follow up only in microsoft.public.inetserver.asp.db
> >
> >
> >
> > "Jacob" <yak2016@comcast.net> wrote in message
> > news:O026FS1PDHA.1148@TK2MSFTNGP11.phx.gbl...

> > > I have a table, SYS_Locations, which lists all of the locations

> available

> > > for a system.
> > > I.E.:
> > > 1 -- Matrix Hall
> > > 2 -- Upstairs
> > > 3 -- Basement
> > > 4 -- Cabinet
> > > 5 -- Storage Building
> > >
> > > Then, I have another table, LOC_TREE_REL which ties the previous

> locations

> > > to one another.
> > > I.E.:
> > > 1 -- 2 (Says Upstairs belongs in Matrix Hall)
> > > 5 -- 3 (Says Basement belongs in Storage Building)
> > > 3 -- 4 (Says the Cabinet is in the Basement which as shown in a

> previous

> > > row, is in the Storage Building.)
> > > All of the entries in LOC_TREE_REL include a self-ownership entry.

(i.e.

> > For

> > > Matrix Hall, their is a 1 -- 1 entry, Upstairs has a 2 -- 2 entry,

etc.

> > >
> > > I need to be able to tie these locations together in some sort of list

> > which

> > > demonstrates heirarchy.
> > >
> > > I'm considering using a drop-down select menu to do it such as:
> > >
> > > Matrix Hall
> > > ---- Upstairs
> > > Storage Building
> > > ---- Basement
> > > ---------Cabinet
> > >
> > > However, I need some help with the code to perform such location

> > connections

> > > on-the-fly. Any help will be greatly appreciated.
> > >
> > > Jacob
> > >
> > >
> > >

> >
> >

>
>

[Mads Holm]

"Aaron Bertrand [MVP]" wrote:

> Might I suggest a slightly simpler schema? This makes your query *much*
> simpler (avoiding nesting, self-joins, or looping), removes a table from

the

> world, and avoids the need to store self-ownership rows.

It is true that this schema makes this particular query much simpler,
but unfortunately it introduces data redundancy and thereby makes
maintenance of the
structure more complicated.

It's a good idea with the ParentID though, but I think it would be better to
make a stored
procedure or table function calculate the layer and topid fields.

Regards
Mads Holm

[Aaron Bertrand - MVP]

> It's a good idea with the ParentID though, but I think it would be better
to

> make a stored
> procedure or table function calculate the layer and topid fields.

So show it...

[Aaron Bertrand - MVP]

> It is true that this schema makes this particular query much simpler,

> but unfortunately it introduces data redundancy and thereby makes
> maintenance of the
> structure more complicated.

I disagree. This seems like it would be a relatively static set of data,
that has more volatility in queries than in structure. And I really don't
think the structure I introduced is all that complicated.

And further, what I like about storing the layer information is that you can
quite simply ask for everything in the top layer (where layer=1) instead of
using convoluted logic to indicate that an object is on the top layer
because its parent = self or parent = 0, or on the 3rd layer by using steps
to count that the object has one parent, and that its parent has one parent,
etc etc.

I was contemplating dropping topid, but I honestly think including that
information in the data makes it clearer -- especially with smaller data
sets when you are manually moving things around.

[Chris Hohmann]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eMHlHI9PDHA.2432@TK2MSFTNGP10.phx.gbl...

> > It's a good idea with the ParentID though, but I think it would be

better

> to

> > make a stored
> > procedure or table function calculate the layer and topid fields.

>
> So show it...

There's a topic in BOL(Books OnLine) called "Expanding Hierarchies" that
discusses this very thing. Joe Celko also provides an excellent
discussion of the set based solution to hierarchical structures in his
book "JOE CELKO'S SQL FOR SMARTIES". In Joe's own words:

[url]http://groups.google.com/groups?selm=8mklvc%24qf5%241%40nnrp1.deja.com[/url]

Note: I have no affiliation with Joe Celko or his book except as an
admirer of both.

HTH
-Chris

[Mads Holm]

Aaron B wrote:

> I disagree. This seems like it would be a relatively static set of data,
> that has more volatility in queries than in structure. And I really don't
> think the structure I introduced is all that complicated.

I don't find it complicated either.
It's just that the redundancy involves a danger of data getting out of sync.
Whose responsibility is that. If you have to do the updating of the
structure as well, you do need to have a very well-behaved application to
make sure that the integrity is preserved
Talking about integrity, you should include both primary and foreign key
constraints.

Using Sql server 7, there's no possibility of adding table functions or
using table variables, so my solution is not quite as elegant as I had
thought:

SET NOCOUNT ON
GO
CREATE TABLE SYS_Locations
(
locid INT primary key,
description VARCHAR(32),
parentid INT,
CONSTRAINT SL_PARENT FOREIGN KEY ( parentid) REFERENCES SYS_LOCATIONS
(locid)
)
GO
INSERT SYS_Locations VALUES(1, 'Matrix Hall', null)
INSERT SYS_Locations VALUES(2, 'Upstairs', 1)
INSERT SYS_Locations VALUES(5, 'Storage Building', null)
INSERT SYS_Locations VALUES(3, 'Basement', 5)
INSERT SYS_Locations VALUES(4, 'Cabinet', 3)

GO
CREATE PROCEDURE HIERARCHY
AS
CREATE TABLE #H
(locid INT,
description VARCHAR(32),
layer INT,
parentid INT,
topid INT
)
SET NOCOUNT ON

DECLARE @ROWCOUNT int
DECLARE @CURLEVEL int

INSERT #H
SELECT locid,description,1,null,locid
FROM SYS_Locations
WHERE parentid is null

Set @CURLEVEL=2
Set @ROWCOUNT=1

WHILE @ROWCOUNT>0
BEGIN
insert #H
SELECT sl.locid,sl.Description,@CURLEVEL,sl.parentid,#H.t opid
FROM #H
INNER JOIN SYS_locations sl on sl.parentid=#H.locid
LEFT join #H h2 on sl.locid=h2.locid
WHERE h2.locid is null

SET @CURLEVEL=@CURLEVEL + 1

SELECT @RowCount=count(*)
FROM #H
INNER JOIN SYS_locations sl on sl.parentid=#H.locid
LEFT join #H h2 on sl.locid=h2.locid
WHERE h2.locid is null
END

SELECT Hierarchy = REPLICATE('--', layer-1)
+ ' ' + description FROM #H
ORDER BY topid, layer
GO

EXEC HIERARCHY

Regards
Mads Holm