SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL for Threaded Discussion (Part II)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/06/2000 :  21:32:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
webguru22 writes "Hi SQL gurus, Here's a twist on the old "threaded message board query" question: I need to come up with a data model that will let me have elements in an "outline" with numbered sub-elements." Actually a paraphrased his question quite a bit. You can see the original question in the forums though.

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 01/31/2001 :  09:44:22  Show Profile  Reply with Quote
SQL for threaded discussions #2

Does this not break when a single hash value in the postkey jumps over 10?

You are doing an ORDER BY on a varchar column with data that looks like this:
1
1.1
1.1.1
1.2.1

That's fine - no problems, until you reach something like this:

1.1.10.1

Which will, I believe, be inserted in the wrong order - i.e. in front of this record:

1.1.2.1

This is clearly a problem if you want to allow more than 10 direct replies to each parent post. Here is a more thorough solution I developed more than a year ago. Change your postkey columns to look like this:

001
001.001
001.001.001
001.002

With the above setup, our 1.1.10.1 will look like this:
001.001.010.001
and will order correctly, falling behind
001.001.002.001
as it should.

You will notice that this allows up to 1,000 direct replies under each parent. Every zero you add to each level will increase the number of possible direct replies by 10.

In ASP I use a config file to define the number of digits I want in each level. That way, if I want to allow 1 million direct replies to any parent, I set the digits param to 6 (000001 - 999999).

Thanks,
Kurt

Go to Top of Page

kaprice
Starting Member

10 Posts

Posted - 12/30/2001 :  20:44:57  Show Profile  Reply with Quote
quote:

Does this not break when a single hash value in the postkey jumps over 10?

You are doing an ORDER BY on a varchar column with data that looks like this:
1
1.1
1.1.1
1.2.1



If I understand the code correctly, the recordset that displays the threaded records is from the newly created temp table:

Create Table #NestedPosts (
SortID int IDENTITY (1,1),
PostID int,
PostKey varchar(200),
PostLevel int )

The sp then appends records to this temp table one at a time in the threaded order. The temp table has SortID, which is an IDENTITY column, so it will automatically number these records in the order they are placed.

The client code would then use SortID as the OrderBy column, so the 1.1.10.1 scheme you described is for NUMBERING only, not for the OrderBy.

(Of course, I could be wrong.)

-Keith
Go to Top of Page

minou30
Starting Member

2 Posts

Posted - 05/17/2009 :  20:19:02  Show Profile  Reply with Quote
How to translate this into MYSQL? (Just learning stored procedures in MYSQL 5.0; have yet to find a good reference guide.)

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000