help needed on Transact - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help needed on Transact

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 10:29:39

Hi there!

Here is the problem-table:

CommentID ParentCommentID AdeedDate AddedBy Body

1 0 12/12/2008 some1 Some1s text
2 1 12/12/2008 some1 Some1s text
3 0 12/12/2008 some1 Some1s text
4 2 12/12/2008 some1 Some1s text
5 2 12/12/2008 some1 Some1s text
6 5 12/12/2008 some1 Some1s text
7 0 12/12/2008 some1 Some1s text

I need to get the following from this table:
Comment
|_______comment
| |_________comment
| |_________comment
|_______comment
Comment
|_______comment
| |_________comment
| |_________comment
| |__________comment
Comment
|_______comment

Is it possible to get the data according to the Parent comment ID on the SQL-side?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:37:21

Make use of recursive cte. something like

;With comment_cte(CommentID, ParentCommentID, AdeedDate, AddedBy, Body, Level) as
(SELECT CommentID, ParentCommentID, AdeedDate, AddedBy, Body,1 AS Level
FROM table
WHERE ParentCommentID=0
UNION ALL
SELECT c.CommentID, c.ParentCommentID, c.AdeedDate, c.AddedBy, c.Body,c.Level +1
FROM table t
INNER JOIN comment_cte c
ON c.CommentID=t.ParentCommentID
)

SELECT REPLICATE(' ',Level * 10) + Body
FROM comment_cte

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 11:19:05

Visakh! Thank You for your reply once again!

Here is what I got finally!

CREATE PROCEDURE dbo.Test_Comments
(
@BlogID int
)

AS
SET NOCOUNT ON

;WITH comment_cte(CommentID, AddedDate, AddedBy, AddedByEmail, AddedByIP, BlogID,
ParentCommentID, Body, Votes, TotalRating, BlogTitle, LEVEL)

AS (SELECT bc.CommentID,
bc.AddedDate,
bc.AddedBy,
bc.AddedByEmail,
bc.AddedByIP,
bc.BlogID,
bc.ParentCommentID,
bc.Body,
bc.Votes,
bc.TotalRating,
b.BTitle AS BlogTitle,
1 AS LEVEL

FROM tbh_Blogs_Comments as bc
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
WHERE ParentCommentID = 0 AND BlogID = @BlogID
UNION ALL

SELECT c.CommentID,
c.AddedDate,
c.AddedBy,
c.AddedByEmail,
c.AddedByIP,
c.BlogID,
c.ParentCommentID,
c.Body,
c.Votes,
c.TotalRating,
b.BTitle AS BlogTitle,
c.Depth + 1

FROM tbh_Blogs_Comments t
INNER JOIN comment_cte c
ON c.CommentID = t.ParentCommentID
)

SELECT REPLICATE('', Depth * 10) + Body

But the procedure says something like: "United table expression is defined but not used". Where do I go wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:20:31

quote:
Originally posted by krainov

Visakh! Thank You for your reply once again!

Here is what I got finally!

CREATE PROCEDURE dbo.Test_Comments
(
@BlogID int
)

AS
SET NOCOUNT ON

;WITH comment_cte(CommentID, AddedDate, AddedBy, AddedByEmail, AddedByIP, BlogID,
ParentCommentID, Body, Votes, TotalRating, BlogTitle, LEVEL)

AS (SELECT bc.CommentID,
bc.AddedDate,
bc.AddedBy,
bc.AddedByEmail,
bc.AddedByIP,
bc.BlogID,
bc.ParentCommentID,
bc.Body,
bc.Votes,
bc.TotalRating,
b.BTitle AS BlogTitle,
1 AS LEVEL

FROM tbh_Blogs_Comments as bc
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
WHERE ParentCommentID = 0 AND BlogID = @BlogID
UNION ALL

SELECT c.CommentID,
c.AddedDate,
c.AddedBy,
c.AddedByEmail,
c.AddedByIP,
c.BlogID,
c.ParentCommentID,
c.Body,
c.Votes,
c.TotalRating,
b.BTitle AS BlogTitle,
c.LEVEL + 1

FROM tbh_Blogs_Comments t
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
INNER JOIN comment_cte c
ON c.CommentID = t.ParentCommentID
)

SELECT REPLICATE('', Depth * 10) + Body
FROM comment_cte
GO

But the procedure says something like: "United table expression is defined but not used". Where do I go wrong?

you missed from part
done some corrections too

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 11:21:15

You have no FROM part for last SELECT statement.
Add "FROM comment_cte"

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 11:35:35

1 More error: I have russian version, so here is my edition of the exception: "Data types - varchar & ntext are not compatible in add operator"

On

CREATE PROCEDURE dbo.Test_Comments
(
@BlogID int
)

AS
SET NOCOUNT ON

;WITH comment_cte(CommentID, AddedDate, AddedBy, AddedByEmail, AddedByIP, BlogID,
ParentCommentID, Body, Votes, TotalRating, BlogTitle, Depth)

AS (SELECT bc.CommentID,
bc.AddedDate,
bc.AddedBy,
bc.AddedByEmail,
bc.AddedByIP,
bc.BlogID,
bc.ParentCommentID,
bc.Body,
bc.Votes,
bc.TotalRating,
b.BTitle AS BlogTitle,
1 AS Depth

FROM tbh_Blogs_Comments as bc
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
WHERE bc.ParentCommentID = 0 AND bc.BlogID = @BlogID
UNION ALL

SELECT c.CommentID,
c.AddedDate,
c.AddedBy,
c.AddedByEmail,
c.AddedByIP,
c.BlogID,
c.ParentCommentID,
c.Body,
c.Votes,
c.TotalRating,
tbh_Blogs.BTitle AS BlogTitle,
c.Depth + 1

FROM tbh_Blogs_Comments t
INNER JOIN comment_cte c
ON c.CommentID = t.ParentCommentID

INNER JOIN tbh_Blogs
ON c.BlogID = tbh_Blogs.BlogID
)

SELECT REPLICATE('', Depth * 10) + Body
FROM comment_cte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:40:21

why do you ntext? use nvarchar(max) instead or cast it explicitly in query

CREATE PROCEDURE dbo.Test_Comments
(
@BlogID int 
)

AS
SET NOCOUNT ON



;WITH comment_cte(CommentID, AddedDate, AddedBy, AddedByEmail, AddedByIP, BlogID, 
ParentCommentID, Body, Votes, TotalRating, BlogTitle, Depth) 

AS (SELECT bc.CommentID, 
bc.AddedDate, 
bc.AddedBy, 
bc.AddedByEmail, 
bc.AddedByIP, 
bc.BlogID, 
bc.ParentCommentID, 
CAST(bc.Body AS nvarchar(max)),
bc.Votes, 
bc.TotalRating,
b.BTitle AS BlogTitle,
1 AS Depth

FROM tbh_Blogs_Comments as bc
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
WHERE bc.ParentCommentID = 0 AND bc.BlogID = @BlogID
UNION ALL

SELECT c.CommentID, 
c.AddedDate, 
c.AddedBy, 
c.AddedByEmail, 
c.AddedByIP, 
c.BlogID, 
c.ParentCommentID, 
CAST(c.Body AS nvarchar(max)),
c.Votes, 
c.TotalRating,
tbh_Blogs.BTitle AS BlogTitle,
c.Depth + 1

FROM tbh_Blogs_Comments t
INNER JOIN comment_cte c
ON c.CommentID = t.ParentCommentID

INNER JOIN tbh_Blogs 
ON c.BlogID = tbh_Blogs.BlogID
)

SELECT REPLICATE('', Depth * 10) + Body
FROM comment_cte

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 11:57:09

The procedure returns error during the transact: "Procedure is break off. Maximum recursion 100 was used before the procedure end "
And in results I have first Level comments and then 100 same second level (first in rank) comment

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:02:56

wat about this?


CREATE PROCEDURE dbo.Test_Comments
(
@BlogID int 
)

AS
SET NOCOUNT ON



;WITH comment_cte(CommentID, AddedDate, AddedBy, AddedByEmail, AddedByIP, BlogID, 
ParentCommentID, Body, Votes, TotalRating, BlogTitle, Depth) 

AS (SELECT bc.CommentID, 
bc.AddedDate, 
bc.AddedBy, 
bc.AddedByEmail, 
bc.AddedByIP, 
bc.BlogID, 
bc.ParentCommentID, 
CAST(bc.Body AS nvarchar(max)),
bc.Votes, 
bc.TotalRating,
b.BTitle AS BlogTitle,
1 AS Depth

FROM tbh_Blogs_Comments as bc
INNER JOIN tbh_Blogs as b
ON bc.BlogID = b.BlogID
WHERE bc.ParentCommentID = 0 AND bc.BlogID = @BlogID
UNION ALL

SELECT t.CommentID, 
t.AddedDate, 
t.AddedBy, 
t.AddedByEmail, 
t.AddedByIP, 
t.BlogID, 
t.ParentCommentID, 
CAST(t.Body AS nvarchar(max)),
t.Votes, 
t.TotalRating,
tbh_Blogs.BTitle AS BlogTitle,
c.Depth + 1

FROM tbh_Blogs_Comments t
INNER JOIN comment_cte c
ON c.CommentID = t.ParentCommentID

INNER JOIN tbh_Blogs 
ON c.BlogID = tbh_Blogs.BlogID
)

SELECT REPLICATE('', Depth * 10) + Body
FROM comment_cte

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 12:03:01

Add OPTION (MAXRECURSION 0) to you last SELECT statement

But I think you have circular reference in your table and thus will the OPTION (MAXRECURSION 0) halt your server.

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 12:15:26

It works now! But where is the rest of Data? It retrieves body only!
And I don't understand how it work ;-) It is too difficult for me...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:17:57

quote:
Originally posted by krainov

It works now! But where is the rest of Data? It retrieves body only!
And I don't understand how it work ;-) It is too difficult for me...

you've given only body in final select. include other fields also according to your need in final select. i gave only body as you had shown only its value in sample output

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-28 : 12:28:40

quote:
Originally posted by visakh16

quote:
Originally posted by krainov

It works now! But where is the rest of Data? It retrieves body only!
And I don't understand how it work ;-) It is too difficult for me...

you've given only body in final select. include other fields also according to your need in final select. i gave only body as you had shown only its value in sample output

Visakh! And how do I sort the result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:37:35

use ORDER BY to sort. on what field you want to sort?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources