December 13, 2011 at 10:49 am
HI all,
I have a requiremnent to count all the subfolders in the given root folder was trying to do it usinf a recursive CTE but my results are not coming properly.
here is the code and test query:
CREATE TABLE [dbo].[test](
[main_id] [int] NULL,
[childid] [int] NULL,
[parentid] [int] NULL,
[name] [varchar](250) NULL,
[Level] [int] NULL
)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 87, 1, 'IA', 'IA', 'IA', 1)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 52, 87, 'MD', 'MD', 'MD', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 47, 87, 'RS', 'RS', 'RS', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 74, 87, 'AS', 'AS', 'AS', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 30, 87, 'CAP', 'CAP', 'CAP', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 36, 87, 'BRD', 'BRD', 'BRD', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 82, 87, 'WTR', 'WTR', 'WTR', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 3, 87, '08 BCC', '08 BCC', '08 BCC', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 78, 87, 'DRF', 'DRF', 'DRF', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 72, 87, 'BIN', 'BIN', 'BIN', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 81, 87, 'CAFF', 'CAFF', 'CAFF', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 89, 87, 'DOCC', 'DOCC', 'DOCC', 2)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 70, 72, 'Test', 'Test', 'Test', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 37, 36, 'Test', 'Test', 'Test', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 88, 74, 'EQQ', 'EQQ', 'EQQ', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 88, 74, 'MNN', 'MNN', 'MNN', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 66, 52, 'JKK', 'JKK', 'JKK', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 67, 52, 'LPP', 'LPP', 'LPP', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 77, 52, 'GRR', 'GRR', 'GRR', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 98, 52, 'FFFF', 'FFFF', 'FFFF', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 0, 52, 'DDD', 'DDD', 'DDD', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 3, 52, 'SSS', 'SSS', 'SSS', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 68, 52, 'FRT', 'FRT', 'FRT', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 29, 52, 'RET', 'RET', 'RET', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 31, 30, 'GET', 'GET', 'GET', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 34, 30, 'MAT', 'MAT', 'MAT', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 52, 30, 'GAT', 'GAT', 'GAT', 3)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 53, 52, 'JAT', 'JAT', 'JAT', 4)
INSERT INTO test ( main_id,childid,parentid,name,name,name,Level) VALUES ( 87, 60, 37, 'JET', 'JET', 'JET', 4)
select * from test order by level
here is the test query ( i am running for 87 id)
WITH cte (childId, ParentId,name,Level ) AS
(
SELECT a.childId, a.parentId, a.name ,a.Level
FROM [172.20.1.158].[LFOnline].dbo.test a
WHERE a.childId= 87
UNION ALL
SELECT a.childId, a.parentId, a.name ,a.Level
FROM [172.20.1.158].[LFOnline].dbo.test a
INNER JOIN cte b ON a.parentid = b.childId
)
select ParentId,childid,name,level from cte order by level
The result for 87 should be 37 as all are subfolders in side it
any help...
Thanks [/font]
December 13, 2011 at 11:35 am
I took the liberty of fixing your sample data.
CREATE TABLE [dbo].[test](
[main_id] [int] NULL,
[childid] [int] NULL,
[parentid] [int] NULL,
[name] [varchar](250) NULL,
[Level] [int] NULL
)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 87, 1, 'IA', 1)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 52, 87, 'MD', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 47, 87, 'RS', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 74, 87, 'AS', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 30, 87, 'CAP', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 36, 87, 'BRD', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 82, 87, 'WTR', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 3, 87, '08 BCC', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 78, 87, 'DRF', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 72, 87, 'BIN', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 81, 87, 'CAFF', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 89, 87, 'DOCC', 2)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 70, 72, 'Test', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 37, 36, 'Test', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 88, 74, 'EQQ', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 88, 74, 'MNN', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 66, 52, 'JKK', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 67, 52, 'LPP', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 77, 52, 'GRR', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 98, 52, 'FFFF', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 0, 52, 'DDD', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 3, 52, 'SSS', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 68, 52, 'FRT', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 29, 52, 'RET', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 31, 30, 'GET', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 34, 30, 'MAT', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 52, 30, 'GAT', 3)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 53, 52, 'JAT', 4)
INSERT INTO test ( main_id,childid,parentid,name,Level) VALUES ( 87, 60, 37, 'JET', 4)
select * from test order by level
WITH cte (childId, ParentId,name,Level ) AS
(
SELECT a.childId, a.parentId, a.name ,a.Level
FROM test a
WHERE a.childId= 87
UNION ALL
SELECT a.childId, a.parentId, a.name ,a.Level
FROM test a
INNER JOIN cte b ON a.parentid = b.childId
)
select ParentId,childid,name,level from cte where level > 1 order by level
Your final select was getting your original row plus the others. If you only want the children just get the records where level > 1. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 11:50 am
Thanks but this is not what I am looking for
once i get this fixed for 87 I will use this function to run for other MAINID's
So basically I need count for 87, like it should just returna count of 37 for 87 folder id...
by this query i am getting all folders and all rows I just need a single row answer saying
ID = 87 Count =37
Hope you got it
Thanks [/font]
December 13, 2011 at 12:09 pm
Learner1 (12/13/2011)
Thanks but this is not what I am looking foronce i get this fixed for 87 I will use this function to run for other MAINID's
So basically I need count for 87, like it should just returna count of 37 for 87 folder id...
by this query i am getting all folders and all rows I just need a single row answer saying
ID = 87 Count =37
Hope you got it
ummm count(*)???
select COUNT(*) from cte where level > 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 12:16 pm
Hmm...This will be runnning for a hardcoded child Id (in this case 87)
What if I run like this changing the where caluse to IN ----Where childId in ( select distinct childid from tablename)
This will change the count(*) to huge number and will not display Count by each ChildID.
So THe result to be produced should be like this
ChildID , Count(*)
87 , 37
hope that makes sense.
Thanks [/font]
December 13, 2011 at 12:21 pm
Learner1 (12/13/2011)
Hmm...that will be runnning for a hardcoded child Id (in this case 87)What if I run like this changing the where caluse to IN ----Where childId in ( select distinct childid from tablename)
This will change the count(*) to huge number and will not display Count by each ChildID.
So THe result to be produced should be like this
ChildID , Count(*)
87 , 37
hope that makes sense.
The target seems to be moving here. What are you trying to accomplish? Is Main_ID really what you are after? You would need to add that to your cte as well as the queries then include that in your output.
;WITH cte (mainID, childId, ParentId,name,Level ) AS
(
SELECT a.main_id, a.childId, a.parentId, a.name ,a.Level
FROM test a
WHERE a.childId= 87
UNION ALL
SELECT a.main_id, a.childId, a.parentId, a.name ,a.Level
FROM test a
INNER JOIN cte b ON a.parentid = b.childId
)
select mainID, COUNT(*) from cte where level > 1 group by mainID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 12:24 pm
Well,There is no MainId column in the SRC table, there is only ChildId and ParentID, I just created that test table as sample data table...
Thanks [/font]
December 13, 2011 at 12:35 pm
So your ddl is not accurate either? It is really hard to figure out what you want when it keeps changing.
Something like this maybe?
create procedure GetSomeCount
(
@ChildID int
) as begin
;WITH cte (childId, ParentId,name,Level ) AS
(
SELECT a.childId, a.parentId, a.name ,a.Level
FROM test a
WHERE a.childId = @ChildID
UNION ALL
SELECT a.childId, a.parentId, a.name ,a.Level
FROM test a
INNER JOIN cte b ON a.parentid = b.childId
)
select @ChildID as ChildID, COUNT(*) as SomeCount from cte where level > 1 --group by @ChildID
end
go
exec GetSomeCount 87
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 12:52 pm
yeah the mainId is actually hard coded by me...
I think this is what I have to do but instead of @ChildID I will have to use Cursor to fetch the Child ID from a table and get count one by one...
Thanks a lot for your Quick Response and help
Thanks [/font]
December 13, 2011 at 1:02 pm
Learner1 (12/13/2011)
yeah the mainId is actually hard coded by me...I think this is what I have to do but instead of @ChildID I will have to use Cursor to fetch the Child ID from a table and get count one by one...
Thanks a lot for your Quick Response and help
Better than a **cough**cursor**cough** you should do this set based. If you want to tackle that I will need some actual details about your structure. Given the recursive cte (which is already row by row processing) and adding another row by row round trip through the same data again and again you would stand to gain quite a performance boost.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 13, 2011 at 2:04 pm
Ohh I stepped out...SET based ..I never tried that ..
What details would you require from me for that....(also do you need a NYQUIL???? :-))
Thanks [/font]
December 13, 2011 at 2:37 pm
Learner1 (12/13/2011)
Ohh I stepped out...SET based ..I never tried that ..What details would you require from me for that....(also do you need a NYQUIL???? :-))
Details...ddl (create table scripts), sample data (insert statements), desired output based on your sample data, clear explanation of exactly what you are trying to do. Take a look at the first link in my signature for best practices on posting this stuff.
It seems that no matter how much NYQUIL I take along comes another that infected with one of those pesky **cough**cursors**cough** :sick:
The main thing you need to do set based processing is a change in your thinking. Instead of thinking about changing the row, think about changing the column. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2011 at 8:22 am
Here is complete code, I hope this will make the query more clear now...
In words the requirement is to count all the Folders including subfolders in the given root folder, also count all the files in that folder.
The indicator for Folder is TYPE ='F' and for FILE if TYPE = 'I'[/b]
CREATE TABLE [dbo].[test](
[childid] [int] NULL,
[parentid] [int] NULL,
[name] [varchar](250) NULL,
[type] [varchar](50) NULL
)
INSERT INTO test ( childid,parentid,name,type) VALUES ( 71, 99, 'JILL', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 61, 99, 'KIMM', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 82, 71, 'WWW', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 23, 29, 'LIOO', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 64, 68, 'TTTT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 87, 1, 'ROOT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 79, 82, 'File678', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 83, 47, 'file43', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 7111, 9911, 'JILL', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6111, 9911, 'KIMM', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 8211, 7111, 'WWW', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 2311, 2911, 'LIOO', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6411, 6811, 'TTTT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 8711, 1, 'ROOT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 7911, 8211, 'File678', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 8311, 4711, 'file43', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 5211, 8711, 'FILE44', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 4711, 8711, 'CCC', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 7411, 8711, 'DDD', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 3011, 8711, 'FILE1', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 8111, 8711, 'FFF', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 8811, 7411, 'GGGG', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 9911, 7411, 'FILE55', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6611, 5211, 'KKKK', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6711, 5211, 'LLLL', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 7711, 5211, 'FILER', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 9811, 5211, 'YYYY', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6811, 4711, 'QQQQ', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 2911, 4711, 'IIII', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 3111, 3011, 'NNNN', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 3411, 3011, 'FILEM', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6911, 3011, 'TTTT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 5311, 8111, 'JAT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 6011, 8111, 'FILE6', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 52, 87, 'FILE44', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 47, 87, 'CCC', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 74, 87, 'DDD', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 30, 87, 'FILE1', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 81, 87, 'FFF', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 88, 74, 'GGGG', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 99, 74, 'FILE55', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 66, 52, 'KKKK', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 67, 52, 'LLLL', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 77, 52, 'FILER', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 98, 52, 'YYYY', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 68, 47, 'QQQQ', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 29, 47, 'IIII', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 31, 30, 'NNNN', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 34, 30, 'FILEM', 'I')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 69, 30, 'TTTT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 53, 81, 'JAT', 'F')
INSERT INTO test ( childid,parentid,name,type) VALUES ( 60, 81, 'FILE6', 'I')
select * from test
Here is my Query
--my CTE query but not giving correct results
WITH cte (childId, ParentId,name,[type] ,Level ) AS
(
SELECT a.childId, a.parentId, a.name , [type] ,1 as Level
FROM test a
WHERE a.childId in( 87 ,8711) --here I have shown only 2 rootids but can Use a query to pass all Ids at once (select childid from test where ParentId =1)
UNION ALL
SELECT a.childId, a.parentId, a.name , a.[type] , Level+1 as Level
FROM test a
INNER JOIN cte b ON a.parentid = b.childId
)
select 'root' ParentId,type,count(*) from cte where level > 1 --and ParentId =1
group by type
Here is the Output that is required from this query:
---Output Required
PRINT 'OUTPUT RESULT:'
SELECT '87' 'ROOT_ID', 'F' 'TYPE', '17' 'COUNT' union
SELECT '87' 'ROOT_ID', 'I' 'TYPE', '8' 'COUNT' union
SELECT '8711' 'ROOT_ID', 'F' 'TYPE', '18' 'COUNT' union
SELECT '8711' 'ROOT_ID', 'I' 'TYPE', '7' 'COUNT'
Just to explain, We have two Root Child Ids here WE can find them using this query
select * from test where ParentId =1
Now We need the total Folder counts and the total File counts in these Root child IDS,it should produce 4 Rows as shown in my output result.
Any help on this.
Thanks [/font]
December 14, 2011 at 9:08 am
Thanks for the ddl and such. You were so close!!! Just need to add the RootID to the original query so you can group by it at the end.
;WITH cte (childId, ParentId, name, [type] ,Level, RootID) AS
(
SELECT a.childId, a.parentId, a.name, [type], 1 as Level, a.childID as RootID
FROM test a
WHERE a.childId in(87, 8711)
UNION ALL
SELECT a.childId, a.parentId, a.name, a.[type], Level + 1, b.RootID-- as Level
FROM test a
INNER JOIN cte b ON a.parentid = b.childId
)
select RootID as ParentId, type, count(*) as [Count] from cte where level > 1
group by type, RootID
order by type, RootID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 14, 2011 at 9:21 am
It works ....Thank you so much Sean...I really appreciate your help.
Thanks [/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply