The right SQL-statement

[Sovetryne]

It seems I am the first on the Internet having a problem with this. So either I am trying something impossible, or I am so stupid that nobody needs to write it down on any homepage.

My problem is some SQL-statement.
I am writing a forum (

http://www.denemarken.prikbord.franl.dk).

I use VBScript / ASP as language, and Microsoft Access 2000 as database. I am having MDAC 2.8 installed on my server.
The table in the database has following fields:

- ID (AutoNumber)
- ReplyID (= 0 if a new message is postet, or "ID" of the original message, if the message is a reply)
- BaseID (ID of the first message in a topic range
- Date
- Time
- Subject
- Text
- IP
- Name
- Email
- Position

All subjects are ordered by reply, which means, that a reply on a message appears in a list right under the original message, with to spaces ( in front of the subject. And if there comes a reply on a reply, it simply adds two more spaces, and appears right under the last message.

Such as these:

Message (ID:1, ReplyID:0, BaseID: 1)
--Re: Message (ID:2, ReplyID:1, BaseID: 1)
----Re: Message (ID:3, ReplyID:2, BaseID: 1)

Now comes the tricky part. Look at this:

Message (ID:1, ReplyID:0, BaseID: 1)
--Re: Message (ID:2, ReplyID:1, BaseID: 1)
----Re: Message (ID:3, ReplyID:2, BaseID: 1)
------Re: Message (ID:4, ReplyID:3, BaseID: 1)
--------Re: Message (ID:5, ReplyID:4, BaseID: 1)
----Re: Message (ID:6, ReplyID:2, BaseID: 1)
------Re: Message (ID:7, ReplyID:6, BaseID: 1)
--------Re: Message (ID:8, ReplyID:7, BaseID: 1)
------Re: Message (ID:9, ReplyID:6, BaseID: 1)
--------Re: Message (ID:10, ReplyID:9, BaseID: 1)

Now my question is: WHICH SQL Statement am I supposed to use? At this moment, I am using

Select ID, ReplyID, Name, Email, IP, Subject, Text, Date, Time, Position, BaseID from forum ORDER BY BaseID, Position, Date, Time ASC

Yours,

Albert van Harten
Denmark

 

[cheerio]

This is from first principles as I have never done a message board.

I assume your problem is how much to indent the messages. My gut feeling is that this is difficult with your existing structure.

What I would do is have another field called depth which is defined as 0 when ReplyID is 0 otherwise 1+depth of the parent. ie what we are doing is moving the logic to record creation.

In your SQL you can then use the Access String or String$ function to create the right number of dashes. Something like string(2*depth,"-&quot as the first field in the SQL

 

[substitute]

Your example is not a good one as you could just sort by ID to get that result. If the next mesage (11) was a reply to message 4 you would want it to be inserted between message 5 and message 6

The problem is you have to traverse the hierarchy to get the order. I have a gut feeling that you could do this whole thing with a recursive query but I don't even know if such things are possible with SQL!

What I did for a family tree report I wrote was to create an index table by repeatedly applying a simple hierarchical lookup until the index included all the records in the original table. While doing this you can count the spaces to add as well.

This can be done conveniently in VBA because you CAN write recursive procedures in VBA.

You need a recursive subroutine AddIndex(ReplyID) and a subroutine to call AddIndex(0)

Essentially you start by adding the first (replyID=0) record that isn't in the index table to the index table. Then recursively call AddIndex to add the first (replyID=1) record that isn't in the index table to the index table. When this call returns the whole tree of replies has been indexed so move to the next (replyID=0) record and loop. When there are NO (ReplyID=X) records not in the index table pop back up a level. When you pop out of the zeroth level you have indexed all the records and you can output them by linking the index table to the forum table.

Sorry this is a bit complicated but it would take me a long time to explain it with full code examples.

 

[Sovetryne]

Cheerio, thanks for your reply.
The problem is not "how to put dashes (or other characters) in front of the subject line", but "how to keep SUBTHREATS together". I already have a field for what you call "depth", I called it "position"

Substitute, also thanks to you for replying. You really understand the problem perfectly. Now I only have to find out how to make a recursive call in VBA... But thanks in advance!!!

Sovetryne

 

[substitute]

Here's the basic code you need.

This is in DAO because I'm a desktop developer, but you should be able to do much the same in ADO for a web application.

Public Sub AddIndex(iReplyTo As Integer)

Dim oDB As DAO.Database
Dim orstSQL As DAO.Recordset
Dim orstIndex As DAO.Recordset

Set oDB = CurrentDb
Set orstIndex = oDB.OpenRecordset("index&quot
' find replies to this message that aren't in the index
Set orstSQL = oDB.OpenRecordset("SELECT ID" & _
" FROM Forum LEFT JOIN [Index] " & _
" ON Forum.ID = [Index].ForumID" & _
" WHERE IndexID is null AND ReplyID = " & iReplyTo)
Do While Not orstSQL.EOF
' Add this reply to the index
With orstIndex
.AddNew
!forumid = orstSQL!ID
.Update
End With
' recursive call to add chain of replies to this reply
AddIndex (orstSQL!ID)
' select the next reply
orstSQL.MoveNext
Loop

End Sub

Public Function CreateIndex()
'
' call this to create the index
'
Dim oDB As DAO.Database

Set oDB = CurrentDb
oDB.Execute ("DELETE * FROM [Index]&quot
AddIndex (0)

End Function

Create a module, use Tools/References to add a reference to DAO 3.6, paste the above code into the module, save it, then create a macro and use the runcode action to run CreateIndex()

You then need a query like this to display the forum in threaded order:

SELECT Forum.ID, Forum.ReplyID
FROM [Index] INNER JOIN Forum ON Index.ForumID = Forum.ID
ORDER BY Index.IndexID;

It doesn't do everything (it could fill in the position code for instance) but it is the basis of a solution. It was a lot easier to do than the family tree!

Ian.

 

[sxschech]

I have a similar question to the one addressed in this thread. I was given a table that contains 2 cols. First Col contains Part Number and Second Col Contains the Part Number below it which then would need to look back at the first col to find the one below itself.

Table:
Part SubPart
501 064
501 050
501 089
..
..
050 221
050 241
..
..
221 222

Below is the logic if done manually to determine the levels to appear on the report for illustrative purpose. The level number will change depending on what part number you start at:

Part Level SubPart Level
501 1 064 2
501 1 050 2
501 1 089 2
..
..
050 2 221 3
050 2 241 3
..
..
221 3 222 4


So in this case, result on report would look this way:
Level Part
1 501
2 050
3 221
4 222

Since parts and subparts could be added anytime, doesn't seem like I could rely on an ID field as the thread's original example does. Any thoughts would be greatly appreciated.

 

[Sovetryne]

Dear Substitute,

I am really very glad for your work! You have done a lot for me. But I am sorry, it doesn't work. I am using ASP (VBScript), and many functions you are using in your code does not work on ASP.
E.g. declarations are without types, so Dim oDB As DAO.Database should be Dim oDB, but it is not possible to declare references. Even modules are not possible is VBScript. Why oh why is Microsoft annoying me

Yours sincerely,

Albert van Harten
Denmark

 

[substitute]

Albert:

I'm afraid I don't work in your programming environment, so I can't sort your question out directly. Does VBscript have the DLOOKUP function? Have a look at this code:

Public Function AddIndexDL(iReplyTo, iLevel)
Dim ID
ID = iReplyTo
Do While Not IsNull(ID)
ID = DLookup("id", "Forum", "ReplyID = " & iReplyTo & " AND [id] > " & ID)
If Not IsNull(ID) Then
Debug.Print Space(iLevel * 2); ID
AddIndexDL ID, iLevel + 1
End If
Loop
End Function

No DAO, no recordsets, no objects, no index, no query. Just call AddIndexDL(0,0)

The above function seems to list IDs in the right order and indents them. You would have to replace the "debug.print" with some code to show the message headers which you could retrieve using further DLOOKUP calls.

sxschech:

The logic for your part explosion problem is basically the same as above. Part is equivalent to ID and SubPart is equivalent to ReplyID.

I'm pleased with that - much simpler than my first post and might even work for you!

 

[Sovetryne]

Substitute,
It seems DLOOKUP does not work. I guess I have to try to write a DLOOKUP-function myself, since VBS does not support it by its own.

So what I am going to do, is rewriting a big part of the code, and I guess I can use a few things from your codes. Many thanks for that.

Albert van Harten
Denmark