A tree of location and site names.. -- sql server mseq

Rhonda Fischer
5/13/2005 8:22:02 AM

Hello,

This is a tuff one not sure if it can be done with an SQL query, I'm
thinking along the lines of an inner join, but I'm not really sure.

I have a table that is used to refer to location_names as nodes and sites as
the leaf nodes it is the site names that I want to return based on a join to
another table using the SiteFK column.

TABLE: TRS_SiteTree
SiteTreePK ParentFK Name SiteFK Path
1 0 All 0
0
2 1 South 0
0.0
3 1 North 0
0.1
4 2 Oxfordshire 0
0.0.0
5 4 Witney 1
0.0.0.0
6 4 Banbury 2
0.0.0.1
7 3 Teeside 0
0.1.0
8 7 Yarm 3
0.1.0.0
9 4 Oxford 4
0.0.0.2
10 3 Yorkshire 5
0.1.1

The SiteFK indicates if the site is a leaf node if it is <> 0 otherwise a
parent
The Path indicates the level of nesting

The leaf node Yorkshire has a parentFK of 3 which matches to the SiteTreePK
of 3 which subsequently has a Parent FK of 1 and a location of North which
sits under All where the Parent FK matches with the SiteTreePK of 1 All.

An illustration of the tree that the table represents:

0 All
|_____0.0 South
| |______0.0.0 Oxfordshire
| |_________ 0.0.0.0 Witney
(leaf 1)
| |_________ 0.0.0.1 Banbury
(leaf 2)
| |_________ 0.0.0.2 Oxford
(leaf 4)
|_____ 0.1 North
|______ 0.1.0 Teeside
| |_________ 0.1.0.0 Yarm (leaf
3)
|______ 0.1.1 Yorkshire (leaf 5)

if you have any suggestions on where I might start with this task this would
be muchly appreciated even if it's just a few ideas to try out.

Thank you kindly
Rhonda

Rhonda Fischer
5/13/2005 8:32:09 AM

ooops, I mean a self join

[quoted text, click to view]

Hugo Kornelis
5/18/2005 12:00:00 AM

[quoted text, click to view]
(snip)

Hi Rhonda,

Sorry for the late reply.

I'm not sure what exactly you're asking. Your message doesn't appear to
have any specific question. Are you looking for generic advice if your
solution is the best one for your problem? Or do you actually need a
query to do a specific job?

In the latter case, please repost. I'd appreciate it if you'd post the
table structure as a CREATE TABLE statement (including all constraints
and properties - any irrelevant columns may be left out, though) and the
sample data as INSERT statements. Also, post the output you'd like to
get from the sameple data.
Check www.aspfaq.com/5006 for some useful hints on how to post.

If you're looking for generic advice on how to deal with hierarchies,
then your best bet is to buy and read "Trees and Hierarchies in SQL" by
Joe Celko. This book describes several ways to model a hierarchy in SQL,
with their pros and cons.

You could also google for "nested sets model" and "adjacency list model"
to find information about two of the most common ways to represent a
hierarchy.

Best, Hugo
--

Rhonda Fischer
5/26/2005 3:17:18 AM

Dear Hugo,

Thank you very much for your considered thought on my questions. Your
thoughts and suggested reference material were helpful to draw this to a
resolution.

Thank you again :)

Cheerio
Rhonda

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : A tree of location and site names..