count of sub query self join

  • 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;

  • 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

  • I've updated my post with the requested data + my sql stored procedure GetPostsThread

  • Thanks, but your sample data looks nothing like your expected output. Can you update that as well?

    -- Gianluca Sartori

  • 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/'

  • It returns no rows, with new and old query.

    What am I missing?

    The only parameter that returns rows is '1'.

    -- Gianluca Sartori

  • 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

  • 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

  • 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/'

  • 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

  • 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;

  • 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

  • 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

  • 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

  • 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