January 26, 2012 at 1:00 am
I've trying to get my code to print out Count of replies in the first table in 3/5/ there is reply and under that is 3/5/8/ and that has 2 replies but i can only print out the ones that have a count not the ones that have 0 or null.
My table consits of PostID int IdentityColumn, ParentID int, Title varchar(100), Sequence is Replicate, Path_String and Owner varchar(50)
i need it to produce the following table:
Path_String Count
--------------- -------------
3/ 1
3/5/ 1
3/5/8/ 2
3/5/8/14/ 0 or null
3/5/8/15/ 0 or null
3/5/9/ 2
3/5/9/10/ 1
3/5/9/10/11/ 0 or null
here is some test data and my GetPostsByThread:
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
PostID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
AddedBy nvarchar(50),
Title nvarchar(MAX),
ParentID INT null,
Path_String nvarchar(MAX)
)
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable (PostID, AddedBy, Title, ParentID, Path_String)
select 1,'kirkdm', 'test', null, '1/' union all
select 2, 'mikedm', 're:test', 1, '1/2/' union all
select 3, 'joelene', 're:test', 2, '1/2/3' union all
select 4, 'christian', 're: test', 3, '1/2/3/4' union all
select 5, 'zeke', 're:test', 2, '1/2/3/5' union all
select 6, 'gabe', 're:test', 2, '1/2/3/6' union all
select 7, 'eva', 're:test', 6, '1/2/3/6/7'
SET IDENTITY_INSERT #mytable OFF
ALTER PROCEDURE [dbo].[sproc_GetPostsByThread](@threadID nvarchar(max))
AS
BEGIN
SET NOCOUNT ON
-- interfering with SELECT statements.
DECLARE @Posts Table(PostID int, AddedBy nvarchar(50), Title varchar(100), ParentID int, Path_String varchar(max));
INSERT INTO @Posts
SELECT PostID, AddedBy, Title, ParentID, Path_String FROM Posts WHERE Path_String Like @threadID + '/%';
;WITH cte (Level, Sequence, PostID, AddedBy, Title, ParentID, Path_String) AS(
SELECT 1 AS Level,
RIGHT(Replicate('0', 8) + CAST(PostID AS varchar(MAX)), 8),
PostID, AddedBy, Title, ParentID, Path_String
FROM @Posts WHERE PostID = @threadID
UNION ALL
SELECT cte.Level + 1, cte.Sequence + RIGHT(REPLICATE('0', 8) + CAST(P.PostID AS varchar(MAX)), 8),
P.PostID, P.AddedBy, P.Title, P.ParentID, P.Path_String
FROM @Posts P
INNER JOIN cte on P.ParentID = cte.PostID
)
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
ORDER BY s.Sequence;
END
This is my sql
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
ORDER BY s.Sequence;
and this is my second sql which only prints out the values with a count i need also the ones that don't have a count they could be null or zero just as long as they are in the table
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy), COUNT(r.ParentID)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
join cte AS r
on s.PostID = r.ParentID
GROUP BY s.Level, s.Sequence, s.PostID, s.AddedBy, s.Title, s.ParentID, s.Path_String, COALESCE (o.AddedBy, s.AddedBy)
ORDER BY s.Sequence;
January 26, 2012 at 1:58 am
Can you please post the table script as a CREATE TABLE statement and some sample data as INSERT statements? Can you also post the expected output based on the sample data?
If in doubt, read the article linked in my signature line and discover how to post to get the best help on the forums.
-- Gianluca Sartori
January 26, 2012 at 2:34 am
I've updated my post with the requested data + my sql stored procedure GetPostsThread
January 26, 2012 at 2:48 am
Thanks, but your sample data looks nothing like your expected output. Can you update that as well?
-- Gianluca Sartori
January 26, 2012 at 3:01 am
I think this is all in correct order use EXEC sproc_GetPostsByThread '3'
select 3,'kirkdm', 'test', null, '3/' union all
select 5,'MikeDM', 're:test', 5, '3/5/' union all
select 8,'Jolene', 're:test', 5, '3/5/8' union all
select 14,'Christian', 're:test', 8, '3/5/8/14/' union all
select 15,'Zeke', 're:test', 8, '3/5/8/15/' union all
select 9,'kirkdm', 're:test', 5, '3/5/9/' union all
select 10,'Crushanin', 're:test', 9, '3/5/9/10/' union all
select 11,'Jolene', 're:test', 10, '3/5/9/11/'
January 26, 2012 at 3:06 am
It returns no rows, with new and old query.
What am I missing?
The only parameter that returns rows is '1'.
-- Gianluca Sartori
January 26, 2012 at 3:22 am
you might need to change the select staement in the stored procedure from this
SELECT PostID, AddedBy, Title, ParentID, Path_String FROM Posts WHERE Path_String Like @threadID + '/%';
to this
SELECT PostID, AddedBy, Title, ParentID, Path_String FROM #mytable WHERE Path_String Like @threadID + '/%';
that might work.
#mytable or Temp..#mytable
January 26, 2012 at 3:29 am
Nope, I already have done that.
Please, copy your code, paste it in a query window in tempdb and execute it. You will see no output for parameter '3'.
-- Gianluca Sartori
January 26, 2012 at 3:35 am
Can you use my other sample data i got it to work if you put a paramter '1' for some reason it didn't work with '3' so can you try with my first sample data please and try that.
1/2/3/ will have 3 as Count
1/2/3/4/ will be 0 or null
1/2/3/5/ will be 0 or null
1/2/3/6 will be 0 or null
INSERT INTO #mytable (PostID, AddedBy, Title, ParentID, Path_String)
select 1,'kirkdm', 'test', null, '1/' union all
select 2, 'mikedm', 're:test', 1, '1/2/' union all
select 3, 'joelene', 're:test', 2, '1/2/3/' union all
select 4, 'christian', 're: test', 3, '1/2/3/4/' union all
select 5, 'zeke', 're:test', 2, '1/2/3/5/' union all
select 6, 'gabe', 're:test', 2, '1/2/3/6/' union all
select 7, 'eva', 're:test', 6, '1/2/3/6/7/'
January 26, 2012 at 3:45 am
This is the results I get.
Level Sequence PostID AddedBy Title ParentID Path_String Owner
----- ----------------------------------- ------- ---------- --------- -------- ------------- -------
1 00000001 1 kirkdm test NULL 1/ kirkdm
2 0000000100000002 2 mikedm re:test 1 1/2/ kirkdm
3 000000010000000200000003 3 joelene re:test 2 1/2/3 mikedm
4 00000001000000020000000300000004 4 christian re: test 3 1/2/3/4 joelene
3 000000010000000200000005 5 zeke re:test 2 1/2/3/5 mikedm
3 000000010000000200000006 6 gabe re:test 2 1/2/3/6 mikedm
4 00000001000000020000000600000007 7 eva re:test 6 1/2/3/6/7 gabe
Can you please add the desired count column to this output?
-- Gianluca Sartori
January 26, 2012 at 3:50 am
this is what i got for a Count but it's misses some rows
put the following in the sored procedure at the bottom in place of the select statement
you'll notice that
1/2/3/4
1/2/3/5
1/2/3/6 are missing
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy), COUNT(r.ParentID)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
join cte AS r
on s.PostID = r.ParentID
GROUP BY s.Level, s.Sequence, s.PostID, s.AddedBy, s.Title, s.ParentID, s.Path_String, COALESCE (o.AddedBy, s.AddedBy)
ORDER BY s.Sequence;
January 26, 2012 at 4:14 am
I'm sorry, but I'm getting lost. Too much confusion.
Let's start from scratch.
This is your table:
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
PostID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
AddedBy nvarchar(50),
Title nvarchar(MAX),
ParentID INT null,
Path_String nvarchar(MAX)
)
This is your sample data:
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable (PostID, AddedBy, Title, ParentID, Path_String)
select 1,'kirkdm', 'test', null, '1/' union all
select 2, 'mikedm', 're:test', 1, '1/2/' union all
select 3, 'joelene', 're:test', 2, '1/2/3' union all
select 4, 'christian', 're: test', 3, '1/2/3/4' union all
select 5, 'zeke', 're:test', 2, '1/2/3/5' union all
select 6, 'gabe', 're:test', 2, '1/2/3/6' union all
select 7, 'eva', 're:test', 6, '1/2/3/6/7'
SET IDENTITY_INSERT #mytable OFF
GO
This is your current stored procedure:
CREATE PROCEDURE [dbo].[sproc_GetPostsByThread](@threadID nvarchar(max))
AS
BEGIN
SET NOCOUNT ON
-- interfering with SELECT statements.
DECLARE @Posts Table(
PostID int,
AddedBy nvarchar(50),
Title varchar(100),
ParentID int,
Path_String varchar(max)
);
INSERT INTO @Posts
SELECT PostID, AddedBy, Title, ParentID, Path_String
FROM #mytable
WHERE Path_String Like @threadID + '/%';
;WITH cte (Level, Sequence, PostID, AddedBy, Title, ParentID, Path_String) AS(
SELECT 1 AS Level,
RIGHT(Replicate('0', 8) + CAST(PostID AS varchar(MAX)), 8),
PostID, AddedBy, Title, ParentID, Path_String
FROM @Posts
WHERE PostID = @threadID
UNION ALL
SELECT cte.Level + 1, cte.Sequence + RIGHT(REPLICATE('0', 8) + CAST(P.PostID AS varchar(MAX)), 8),
P.PostID, P.AddedBy, P.Title, P.ParentID, P.Path_String
FROM @Posts P
INNER JOIN cte
ON P.ParentID = cte.PostID
)
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
ORDER BY s.Sequence;
END
This procedure produces this output:
EXEC sproc_GetPostsByThread '1'
Level Sequence PostID AddedBy Title ParentID Path_String Owner
----- ----------------------------------- ------- ---------- --------- -------- ------------- -------
1 00000001 1 kirkdm test NULL 1/ kirkdm
2 0000000100000002 2 mikedm re:test 1 1/2/ kirkdm
3 000000010000000200000003 3 joelene re:test 2 1/2/3 mikedm
4 00000001000000020000000300000004 4 christian re: test 3 1/2/3/4 joelene
3 000000010000000200000005 5 zeke re:test 2 1/2/3/5 mikedm
3 000000010000000200000006 6 gabe re:test 2 1/2/3/6 mikedm
4 00000001000000020000000600000007 7 eva re:test 6 1/2/3/6/7 gabe
You want a different output, which involves some kind of count (that I don't understand).
You changed your code to incorporate that count column:
ALTER PROCEDURE [dbo].[sproc_GetPostsByThread](@threadID nvarchar(max))
AS
BEGIN
SET NOCOUNT ON
-- interfering with SELECT statements.
DECLARE @Posts Table(
PostID int,
AddedBy nvarchar(50),
Title varchar(100),
ParentID int,
Path_String varchar(max)
);
INSERT INTO @Posts
SELECT PostID, AddedBy, Title, ParentID, Path_String
FROM #mytable
WHERE Path_String Like @threadID + '/%';
;WITH cte (Level, Sequence, PostID, AddedBy, Title, ParentID, Path_String) AS(
SELECT 1 AS Level,
RIGHT(Replicate('0', 8) + CAST(PostID AS varchar(MAX)), 8),
PostID, AddedBy, Title, ParentID, Path_String
FROM @Posts
WHERE PostID = @threadID
UNION ALL
SELECT cte.Level + 1, cte.Sequence + RIGHT(REPLICATE('0', 8) + CAST(P.PostID AS varchar(MAX)), 8),
P.PostID, P.AddedBy, P.Title, P.ParentID, P.Path_String
FROM @Posts P
INNER JOIN cte
ON P.ParentID = cte.PostID
)
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy), COUNT(r.ParentID) AS cnt
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
INNER JOIN cte AS r
ON s.PostID = r.ParentID
GROUP BY s.Level, s.Sequence, s.PostID, s.AddedBy, s.Title,
s.ParentID, s.Path_String, COALESCE (o.AddedBy, s.AddedBy)
ORDER BY s.Sequence;
END
Once changed the code, this is what you get:
EXEC sproc_GetPostsByThread '1'
Level Sequence PostID AddedBy Title ParentID Path_String Owner cnt
----------- ------------------------- ------- ------- -------- -------- ----------- ------- ---
1 00000001 1 kirkdm test NULL 1/ kirkdm 1
2 0000000100000002 2 mikedm re:test 1 1/2/ kirkdm 3
3 000000010000000200000003 3 joelene re:test 2 1/2/3 mikedm 1
3 000000010000000200000006 6 gabe re:test 2 1/2/3/6 mikedm 1
Now, what should the output look like?
Please, don't describe it, just show us the whole expected output.
-- Gianluca Sartori
January 26, 2012 at 4:25 am
I want it to include 1/2/3/4 count = 0, 1/2/3/5/ count = 0, 1/2/3/6 count= 0, these are missing because there is no count fopr them but i need to include them in the result
other columns included plus the other rows
path count
1/2/3/4 0 or null
1/2/3/5 0 or null
1/2/3/6 0 or null
January 26, 2012 at 4:36 am
OK, I think I got it.
Sorry for my thick head.
ALTER PROCEDURE [dbo].[sproc_GetPostsByThread](@threadID nvarchar(max))
AS
BEGIN
SET NOCOUNT ON
-- interfering with SELECT statements.
DECLARE @Posts Table(
PostID int,
AddedBy nvarchar(50),
Title varchar(100),
ParentID int,
Path_String varchar(max)
);
INSERT INTO @Posts
SELECT PostID, AddedBy, Title, ParentID, Path_String
FROM #mytable
WHERE Path_String Like @threadID + '/%';
;WITH cte (Level, Sequence, PostID, AddedBy, Title, ParentID, Path_String) AS(
SELECT 1 AS Level,
RIGHT(Replicate('0', 8) + CAST(PostID AS varchar(MAX)), 8),
PostID, AddedBy, Title, ParentID, Path_String
FROM @Posts
WHERE PostID = @threadID
UNION ALL
SELECT cte.Level + 1, cte.Sequence + RIGHT(REPLICATE('0', 8) + CAST(P.PostID AS varchar(MAX)), 8),
P.PostID, P.AddedBy, P.Title, P.ParentID, P.Path_String
FROM @Posts P
INNER JOIN cte
ON P.ParentID = cte.PostID
)
SELECT s.Level, s.Sequence, s.PostID, s.AddedBy,
s.Title, s.ParentID, s.Path_String,
Owner = COALESCE(o.AddedBy, s.AddedBy),
cnt = (
SELECT COUNT(*)
FROM cte AS r
WHERE ParentId = s.PostId
)
FROM cte AS s
LEFT OUTER JOIN cte AS o
ON s.ParentID = o.PostID
ORDER BY s.Sequence;
END
-- Gianluca Sartori
January 26, 2012 at 4:41 am
Thats it thanks heaps you got it working I appreciate that and you're help cheers dude
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply