RE: A tree of location and site names..

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