A tree of location and site names..

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

.

Relevant Pages

  • RE: A tree of location and site names..
    ... "Rhonda Fischer" wrote: ... > 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 ...
    (microsoft.public.sqlserver.mseq)
  • Supress blank attribute.
    ... a.FK_PageContentID FROM tblMenuParent a INNER JOIN tblPageContent b ON ... b.FK_PageTypeID = c.PK_PageTypeID) as Parent ... That results in the following XML ...
    (microsoft.public.sqlserver.xml)