MS SQL Server MSEQ - A tree of location and site names..

Author

A tree of location and site names..

Rhonda Fischer

2005-05-13, 11:23 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

2005-05-13, 11:23 am

ooops, I mean a self join

"Rhonda Fischer" wrote:

> 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
>

Hugo Kornelis

2005-05-18, 7:24 am

On Fri, 13 May 2005 08:22:02 -0700, Rhonda Fischer wrote:

>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.
(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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Rhonda Fischer

2005-05-26, 7:23 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

"Hugo Kornelis" wrote:

> On Fri, 13 May 2005 08:22:02 -0700, Rhonda Fischer wrote:
>
> (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
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Sponsored Links