How do I query this kind of problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

How do I query this kind of problem?

Author

Topic

wappibz
Starting Member

3 Posts

Posted - 2011-01-10 : 03:04:24

Hi, I have a forum, people can post a topic, and in a topic, people can reply it, the special thing is, people even can reply to the replys. So I designed a table to save all the reply and reply to replys, for example, I have a topic, the topic ID is 1, and there are replys ABCDE.., and reply to A we have got a b c i, no reply to B, d e j to C, and so on. I designed this table has 3 fields, N, link and time, N means the reply Id, link means the ID reply link to, time means post time.
so here is the example table:
N link time
A 1 2011-01-09 20:57:19.687
a A 2011-01-09 20:57:24.173
B 1 2011-01-09 20:57:24.297
b A 2011-01-09 20:57:31.587
c A 2011-01-09 20:57:35.870
C 1 2011-01-09 20:57:48.647
d C 2011-01-09 20:57:57.190
e C 2011-01-09 20:58:00.667
D 1 2011-01-09 20:58:04.370
f D 2011-01-09 20:58:10.333
E 1 2011-01-09 20:58:14.763
g E 2011-01-09 20:58:19.697
h E 2011-01-09 20:58:23.427
i A 2011-01-09 21:27:00.937
j C 2011-01-09 21:27:07.983

In the topic page, I have to list all replys to this topic, and all replys to the replys should list too, and in one page, it only shows 10 replys( the number of replys to reply does not count).
It should look like:
A
-a
-b
-c
-j
B
C
-d
-e
D
-f
E
-g
-h

Can anyone teach me how to query this? or I should save replys in a table, and replys to reply in another table?
Thanks!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-10 : 05:10:37

As far as I can understand this is a standard parent/child hierarchy that can be solved using what is called a "recursive cte" -> http://msdn.microsoft.com/en-us/library/ms186243.aspx

The most common way to design this is with a self referencing column like this:

CREATE TABLE [dbo].[posts](
	[TopicID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
	[ParentTopicID] [int] NULL,
	[TopicContent] [varchar](max) NULL,
)

ALTER TABLE [dbo].[posts]  WITH CHECK 
	ADD CONSTRAINT [FK_posts_posts] FOREIGN KEY([ParentTopicID]) REFERENCES [dbo].[posts] ([TopicID])
ALTER TABLE [dbo].[posts] CHECK CONSTRAINT [FK_posts_posts]

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:47:27

another way is using hierarchyid in sql 2008

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-10 : 12:44:59

What if there are more than 26 replies?
Have you considered using integer for reply numbering?

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-10 : 17:04:17

Google the "Nested Sets model" for hierarchies.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources