?
Solved

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

Posted on 2009-04-09
6
Medium Priority
?
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24114056
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
ID: 24114059
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
ID: 24119405
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 24121973
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
ID: 24122076
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
ID: 31568561
i will try this thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 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