Hierarchical Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchical Query

Author

Topic

nightmareweaver
Starting Member

2 Posts

Posted - 2009-02-25 : 10:34:02

Table A

Project_task_id Parent_project_task_id Project_task_key
--------------------------------------------------------------------------------------
10| -1| 1111|
101| 10| 2222|
102| 10| 3333|
1011| 101| 4444|
1012| 101| 5555|
1021| 102| 6666|
20| -1| 7777|
201| 20| 8888|

Table B
Project_task_key Scheduled effort
-----------------------------------------------------------
1111 Not null
2222 Null
3333 Null
4444 Null
5555 Null
6666 Null
7777 Not null
8888 Not null

The node of the of the hierarchy has always the parent_project_id as -1
I have to find the parent tasks for which the scheduled effort is not null but the scheduled efforts of the child records are null

For the above case
The query should return
Project_task_id Parent_project_task_id Project_task_key
------------------------------------------------------------------------------------------
10 -1 1111
-------------------------------------------------------------------------------------------
This is the query I am working on
(select project_task_id,parent_project_task_id,project_task_key
from
a
start with parent_project_task_id =1
connect by prior.project_task_id)X

(select sched_effort,project_task_key
from
b)Y

where X.project_task_key = Y.project_task_key)

Can anyone please help me with a detailed query for the above?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:39:03

are you using sql 2005? if yes, have a look at common table expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

nightmareweaver
Starting Member

2 Posts

Posted - 2009-02-25 : 10:47:31

I am very new to Sql writing.

Yes I am using Sql 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources