tree - record has children / last member in list

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 2000 Forums

Transact-SQL (2000)

tree - record has children / last member in list

Author

Topic

raisor
Starting Member

28 Posts

Posted - 2004-08-05 : 10:16:47

Hello Everyone,

Well, I'm still working on the SQL for Threaded Discussion Forums ( http://www.sqlteam.com/item.asp?ItemID=1353 ) And now I've come to a point where I would like to have 2 things (hopefully through TSQL)

'Find out if a record has children
'Find out if a record is the last member in the list or not

How would I be able to accomplish this?

THANKS!

As on the webpage, the query I'm using is:

SELECT Subject = convert(varchar, SPACE(2 * (PostLevel - 1) ) + Subject),
PostID,
ThreadID,
ParentID,
PostLevel,
SortOrder,
PostDate = convert(varchar(19), PostDate, 120)
FROM Posts
ORDER BY ThreadID, SortOrder

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-05 : 11:17:33

Record has children:

Select case when exists(Select 1 from Posts Where ParentId = postId) then 'Children' else 'No Children' end

Last memeber in the list? Last memeber in the entire list or Last memeber in the thread?

entire list:
Select posts.*
From posts
Inner Join
(Select threadId, max(sortOrder) From posts where threadId = (Select max(ThreadId) From Posts) Group BY ThreadId) as A
On posts.threadId = A.threadId
and posts.sortOrder = A.sortOrder

thread:
Select *
From posts
Where threadId = @threadId and sortOrder = (Select max(SortOrder) From posts where threadId = @threadId)

Corey

raisor
Starting Member

28 Posts

Posted - 2004-08-05 : 14:24:49

Wow....Thanks alot!,

I'm gonna work on it tomorrow and try it all out!, thanks your your fast response!!!

quote:
Originally posted by Seventhnight

Record has children:

Select case when exists(Select 1 from Posts Where ParentId = postId) then 'Children' else 'No Children' end

Last memeber in the list? Last memeber in the entire list or Last memeber in the thread?

entire list:
Select posts.*
From posts
Inner Join
(Select threadId, max(sortOrder) From posts where threadId = (Select max(ThreadId) From Posts) Group BY ThreadId) as A
On posts.threadId = A.threadId
and posts.sortOrder = A.sortOrder

thread:
Select *
From posts
Where threadId = @threadId and sortOrder = (Select max(SortOrder) From posts where threadId = @threadId)

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources