Solved

select most senior node out of a result set representing a hierarchy of nodes

Posted on 2009-04-09
6
417 Views
Last Modified: 2012-06-27
I have a table the stores data of hierarchical entities.

eg

id     |      tree      |     parent_id     |       date_created
======================================
1          1.                     null                        2/1/2009
2          1.2.                  1                            2/1/2009
3          3.                     null                        1/1/2009  
4          3.4                   3                            2/1/2009
5          3.4.5.               4                            2/2/2009

If i query like this
select * from table where date_created >= 2/1/2009, my result set looks like this

id     |      tree      |     parent_id     |       date_created
======================================
1          1.                     null                        2/1/2009
2          1.2.                  1                            2/1/2009
4          3.4                   3                            2/1/2009
5          3.4.5.               4                            2/2/2009

However, I'm not interested in all of the children. I only want to select the top most parent with a single select without using a CTE
so I want:

id     |      tree      |     parent_id     |       date_created
======================================
1          1.                     null                        2/1/2009
4          3.4                   3                            2/1/2009

Note that in the result set above, the top most parent returned might still have a parent in the table (meaning it doesn't necessarily need to be at the root of the tree), but b/c I'm only interested at the most senior parent after a certain date, it should be returned in my final result set, even though technically its a child of some other node in the table.

The tree column is a indexed varchar.

Does anyone have a concise way of doing this?

Thanks!
0
Comment
Question by:alexk23
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Hope this is what you require:

Replace the '?' with date values accordingly

Have tested and is working fine
SELECT i.id, i.tree, i.parent_id, i.date_created

FROM id i INNER JOIN (

SELECT min(id) ID

FROM id 

WHERE parent_id IS NOT NULL

GROUP BY substring(tree, 1, 2) ) t1 ON t1.id = i.id

LEFT OUTER JOIN (

SELECT min(id) id

FROM id 

WHERE parent_id IS NOT NULL

AND date_created = ?

GROUP BY substring(tree, 1, 2)) t2 ON t2.id = i.id

Open in new window

0
 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
try this:

select * from table where date_created >= 2/1/2009 and (parent_id is null or parent_id in(select id from table where parent_id is null))

Hope this help
0
 

Author Comment

by:alexk23
Comment Utility
rrjegan17, does your solution depend on knowing the length of each ID in the bread crumb. IE
substring(tree,1,2) would not work on a tree that looks like 1234.4567.89001 right?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
Yes, it does, and it relies on checking of the first level only. The following works with MSSQL 2005 (not 2000 because of except keyword).

select * from tbl where date_created >= '2/1/2009'

except

select child.* from tbl child join tbl parent

on child.date_created >= '2/1/2009' and parent.date_created >= '2/1/2009' and child.tree like parent.tree+'%' and parent.id != child.id

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Slight Modification to accomodate that in my query. Removed Hardcoding of 1, 2
Tested it and revert me
SELECT i.id, i.tree, i.parent_id, i.date_created

FROM id i INNER JOIN (

SELECT min(id) ID

FROM id 

WHERE parent_id IS NOT NULL

GROUP BY substring(tree, 1, charindex('.',tree)) ) t1 ON t1.id = i.id

LEFT OUTER JOIN (

SELECT min(id) id

FROM id 

WHERE parent_id IS NOT NULL

AND date_created = getdate() - 30

GROUP BY substring(tree, 1, charindex('.',tree))) t2 ON t2.id = i.id

Open in new window

0
 

Author Closing Comment

by:alexk23
Comment Utility
i will try this thanks!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now