[SQL] Threaded comments

Hi everyone,

This has been confusing me a lot.

Table data

+------+-----------+------------+
|  ID  |  REPLY_TO |  TIMESTAMP |
+------+-----------+------------+
|  1   |   0       |   xx       |
|  2   |   1       |   xx       |
|  3   |   2       |   xx       |
|  4   |   1       |   xx       |
|  5   |   0       |   xx       |
|  6   |   5       |   xx       |
|  7   |   3       |   xx       |
+------+-----------+------------+

Code (markup):

REPLY-TO refers to ID where a REPLY-TO is a reply to ID.

Now basically I need an SQL query that will allow me to order these like so:

[1] (New thread/reply to 0)
	[2] (Reply to 1)
		[3] (Reply to 2)
			[7] (Reply to 3)
	[4] (Reply to 1)
[5] (New thread/reply to 0)

Code (markup):

I've tried messing around with ORDER BY but I'm not having any luck, can anyone help?

Cheers,
Ross

 

I don't know about doing it all in one query, but you can use a recursive function to loop through each result. I'm not sure what language you are using (php or .net) so here is an example of a recursive algorythm with pseudo code:

// FIRST LOOP THROUGH ALL PARENT IDS
SELECT ID AS thisId FROM table WHERE REPLY_TO = 0

// NOW PRINT OUT ALL THE TOP-LEVEL THREADS ONE BY ONE
FOR EACH thisId
	print thisId

	// NOW CALL A FUNCTION TO GET ALL CHILD THREADS
	loopData(thisId)
Next


// THIS IS A RECURSIVE FUNCTION
// IT WILL CONTINUE TO CALL ITSELF UNTIL IT FINDS NO CHILD THREADS
function loopData(thisId)
SELECT ID As newId FROM table WHERE REPLY_TO = thisId

print newId

IF NO RESULTS
	exit function
ELSE
	loopData(newId)
END IF

end function

Code (markup):