Hi All,

I need to write to find out the number of hours spent for a project and my table looks like this.

Desc activity:

mysql> desc agile_activity;
+--------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| ID | decimal(10,0) | NO | PRI | | |
| CLASS | decimal(10,0) | NO | | | |
| SUBCLASS | decimal(10,0) | NO | | | |
| NAME | varchar(800) | YES | | | |
| ACTIVITY_NUMBER | varchar(150) | NO | | | |
| STATUS | decimal(10,0) | YES | | | |
| STATUS_TYPE | decimal(10,0) | YES | | | |
| OWNER | decimal(10,0) | NO | | | |
| DELEGATED_OWNER | decimal(10,0) | YES | | | |
| PARENT_ID | decimal(10,0) | NO | | | |
| ROOT_ID | decimal(10,0) | NO | | | |




My Project structure in agile PPM looks like this

PGM102345
PH1234
T12345
T45678
PH47676
T87989
T23499
PH812323
T12545
G787878

I need to find out the hiearchy of my project where in i need to display my project name along with PHases that are part of the project and the tasks associated to each phase.

my output should look like this

PGM2354 PH45454 T79832
T232323

PH45458 T21323
T12323

Its more like CTE in SQL server

in this table the only way to find this realationship is to have parent_id, root_id relationships, all the tasks are tied to its parents PHase and all the root_id of these phases and tasks are related tot eh PGM(project).


Activity_number field stores all the project, phase, task id details like PGM123(project) PH23232 and T244343 and the name field stores the projectname, phase and task names


Please can someone help me in writing a query for this

I am not a full time DB person and i would appreciate any help on this

Thank You,
KK