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;

Complex Query

Hi,
I have a tree database with the following fields:
- themeid
- description
- parent id
I have the following registries:
Themeid description parentid
1 food null
2 cars null
3 others null
4 meat 1
But i need to generate a list like:
Food + (level or depth, in this case 1)
Meat + (level or depth in this case 2)
Cars + (level or depth, in this case 1)
Others + (level or depth, in this case 1)
Meat + (level or depth, in this case 1)
Im trying to figure out how to use the with clause to get the list with the
level or depth or each registry, but at this moment i dont have idea about
how to do it.
Any help would be appreciated.
Thanks a lot.
Kind regards.
This question was asked and answered in the programming group. Please don't
post the same question in multiple groups as this can cause duplication of
effort.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Josema" <Josema@.discussions.microsoft.com> wrote in message
news:4A9CF7D0-E8BA-4054-9F0C-53F521D0EC9C@.microsoft.com...
> Hi,
> I have a tree database with the following fields:
> - themeid
> - description
> - parent id
> I have the following registries:
> Themeid description parentid
> 1 food null
> 2 cars null
> 3 others null
> 4 meat 1
> But i need to generate a list like:
> Food + (level or depth, in this case 1)
> Meat + (level or depth in this case 2)
> Cars + (level or depth, in this case 1)
> Others + (level or depth, in this case 1)
> Meat + (level or depth, in this case 1)
> Im trying to figure out how to use the with clause to get the list with
> the
> level or depth or each registry, but at this moment i dont have idea about
> how to do it.
> Any help would be appreciated.
> Thanks a lot.
> Kind regards.
>
|||Here is one way using recursive CTE in SQL Server 2005:
CREATE TABLE Themes (
themeid INT PRIMARY KEY,
description VARCHAR(35),
parentid INT REFERENCES Themes(themeid));
INSERT INTO Themes VALUES (1, 'food', NULL);
INSERT INTO Themes VALUES (2, 'cars', NULL);
INSERT INTO Themes VALUES (3, 'others', NULL);
INSERT INTO Themes VALUES (4, 'meat', 1);
WITH ThemesPath
AS
(SELECT themeid, description, parentid,
CAST('.' + CAST(themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)) AS theme_path,
1 AS depth
FROM Themes
WHERE parentid IS NULL
UNION ALL
SELECT T.themeid, T.description, T.parentid,
CAST(P.theme_path + CAST(T.themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)),
P.depth + 1
FROM Themes AS T
JOIN ThemesPath AS P
ON T.parentid = P.themeid)
SELECT themeid, description, parentid, depth
FROM ThemesPath
ORDER BY theme_path;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Complex Query

Hi,
I have a tree database with the following fields:
- themeid
- description
- parent id
I have the following registries:
Themeid description parentid
1 food null
2 cars null
3 others null
4 meat 1
But i need to generate a list like:
Food + (level or depth, in this case 1)
Meat + (level or depth in this case 2)
Cars + (level or depth, in this case 1)
Others + (level or depth, in this case 1)
Meat + (level or depth, in this case 1)
Im trying to figure out how to use the with clause to get the list with the
level or depth or each registry, but at this moment i dont have idea about
how to do it.
Any help would be appreciated.
Thanks a lot.
Kind regards.This question was asked and answered in the programming group. Please don't
post the same question in multiple groups as this can cause duplication of
effort.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Josema" <Josema@.discussions.microsoft.com> wrote in message
news:4A9CF7D0-E8BA-4054-9F0C-53F521D0EC9C@.microsoft.com...
> Hi,
> I have a tree database with the following fields:
> - themeid
> - description
> - parent id
> I have the following registries:
> Themeid description parentid
> 1 food null
> 2 cars null
> 3 others null
> 4 meat 1
> But i need to generate a list like:
> Food + (level or depth, in this case 1)
> Meat + (level or depth in this case 2)
> Cars + (level or depth, in this case 1)
> Others + (level or depth, in this case 1)
> Meat + (level or depth, in this case 1)
> Im trying to figure out how to use the with clause to get the list with
> the
> level or depth or each registry, but at this moment i dont have idea about
> how to do it.
> Any help would be appreciated.
> Thanks a lot.
> Kind regards.
>|||Here is one way using recursive CTE in SQL Server 2005:
CREATE TABLE Themes (
themeid INT PRIMARY KEY,
description VARCHAR(35),
parentid INT REFERENCES Themes(themeid));
INSERT INTO Themes VALUES (1, 'food', NULL);
INSERT INTO Themes VALUES (2, 'cars', NULL);
INSERT INTO Themes VALUES (3, 'others', NULL);
INSERT INTO Themes VALUES (4, 'meat', 1);
WITH ThemesPath
AS
(SELECT themeid, description, parentid,
CAST('.' + CAST(themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)) AS theme_path,
1 AS depth
FROM Themes
WHERE parentid IS NULL
UNION ALL
SELECT T.themeid, T.description, T.parentid,
CAST(P.theme_path + CAST(T.themeid AS VARCHAR(8)) + '.'
AS VARCHAR(2000)),
P.depth + 1
FROM Themes AS T
JOIN ThemesPath AS P
ON T.parentid = P.themeid)
SELECT themeid, description, parentid, depth
FROM ThemesPath
ORDER BY theme_path;
HTH,
Plamen Ratchev
http://www.SQLStudio.com