complicated sql condition query

Hi,

hope someone can help or suggest something to help me with the issue.

I have a list of projects. this list contains all master (parent) and all subprojects(child). when I click on a project I want to be able to retreive information about that project and it's subprojects. here is my delema. I want my sql query to check if project is master or sub. if master then get all data for this project and its subprojects but if it's sub then get data only for that sub. below is a sample data that I hope it clear things up

parent_ID child_id Type projname

--

100 100 P parent_X_proj

100 25 C child_X_proj

100 29 C child_X_proj2

200 200 P parent_Z

300 300 P parent_Y

etc................

this is how my table is constructed. my application passes child_id and what I want is if someone clicks on parent_X_proj I want to be able to retreive the three projects (100,25,29) but if someone clicks on child project (29) then I want only that project.

so I want my query to look for the type and if my type is P (parent) then get all project where parent_id = 100(for example) but if type= C then get child_id = 29.

I know it could be done in stored procedure buy my application cannon executre SP but only sql statements.

Thank you for any help

which version of SS are you using?

declare @.child_id int

set @.child_id = 100

;with proj

as

(

select

child_id, projname, type, 1 as lvl

from

dbo.t1

where

child_id = @.child_id

union all

select

c.child_id, c.projname, c.type, p.lvl + 1 as lvl

from

dbo.t1 as c

inner join

proj as p

on p.child_id = c.parent_id

and ((p.lvl = 1 and p.type = 'P') or (p.lvl > 1 and p.type = 'C'))

)

select

*

from

proj;

AMB

P.S. not tested

|||

thank you. I'm using SS 2005. as I mentioned in my post that I can't use Stored Procedure since my application can only execute plain sql statement.

thanks

|||

AMB has given you the answer. Your application just needs to form the query and execute it.

Code Snippet

;with proj

as

(

select

child_id, projname, type, 1 as lvl

from

dbo.t1

where

child_id = <selectedValue_goes_here>

union all

select

c.child_id, c.projname, c.type, p.lvl + 1 as lvl

from

dbo.t1 as c

inner join

proj as p

on p.child_id = c.parent_id

and ((p.lvl = 1 and p.type = 'P') or (p.lvl > 1 and p.type = 'C'))

)

select * from proj;