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

A pesky recordset

I have the following User_Log table:
ID User Location Use_Date
1 1234 RR 1/5/05
2 1234 KT 1/10/05
3 1234 ZF 1/20/05
4 1259 ZF 1/13/05
5 1259 KT 1/19/05
I need a T-SQL statement that will get me the two user records with the most
recent Use_date. In this case it would be the fields with the IDs of 3 and
5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
date is 1/19/05. If I do a
SELECT User, max(Service Date)
FROM User_Log
GROUP BY User
I lose the location. If I include the Location in my query, I obviously get
a record for each location. So how do I get the best of both worlds;
Grouping on the user to get the max date but not losing the location of the
record that qualifies in the max statement?
Thanks,
JonSELECT user, location, use_date
FROM User_Log AS L
WHERE use_date =
(SELECT MAX(use_date)
FROM User_Log
WHERE user = L.user)
David Portas
SQL Server MVP
--|||SELECT User, Location, Use_Date
FROM User_log ul
WHERE Used_Date = (SELECT MAX(Use_Date) FROM User_log ul2
WHERE ul.User = ul2.User)
or
SELECT ul.User, ul.Location, ul.Use_Date
FROM User_log ul
INNER JOIN
(SELECT User, MAX(Use_Date) AS max_use_date FROM User_log ) ul2
ON ul.User = ul2.User AND ul.Use_date = ul2.max_use_date
Although both of them assume that the combination of User and Use_date is
unique.
--
Jacco Schalkwijk
SQL Server MVP
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:uYQavE7DFHA.3728@.TK2MSFTNGP14.phx.gbl...
>I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the
> most recent Use_date. In this case it would be the fields with the IDs of
> 3 and 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent
> service date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously
> get a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of
> the record that qualifies in the max statement?
> Thanks,
> Jon
>|||SELECT TOP 2
Id,
User,
Location,
Use_Date
FROM
Users
ORDER BY
Use_Date DESC
"Jon Westmore" wrote:

> I have the following User_Log table:
> ID User Location Use_Date
> 1 1234 RR 1/5/05
> 2 1234 KT 1/10/05
> 3 1234 ZF 1/20/05
> 4 1259 ZF 1/13/05
> 5 1259 KT 1/19/05
> I need a T-SQL statement that will get me the two user records with the mo
st
> recent Use_date. In this case it would be the fields with the IDs of 3 an
d
> 5 as 1234's most recent Use_Date is 1/20/05 and 1259's most recent service
> date is 1/19/05. If I do a
> SELECT User, max(Service Date)
> FROM User_Log
> GROUP BY User
> I lose the location. If I include the Location in my query, I obviously g
et
> a record for each location. So how do I get the best of both worlds;
> Grouping on the user to get the max date but not losing the location of th
e
> record that qualifies in the max statement?
> Thanks,
> Jon
>
>