Complex sort for forum software

[bigmatt19]

On the one hand I hope this is simple, but on the other I'd like to think of myself as being pretty good with this stuff  ;)... so here's the issue... I am creating a threaded message forum from scratch.  I have figured out a way to display the threads using a recursive algorithm... but it seems inefficient.  The forum would be sorta like slashdot's in that the replies to each post will be nested...

I have a two tables: messages and messagerelation.  Messages contains:
[b][u]messages[/u][/b]
[table]
[tr]
[td][b]messageID[/b][/td]
[td][b]parentID[/b][/td]
[td][b]etc.[/b][/td]
[/tr]
[tr][td]0[/td][td][i]NULL[/i][/td][td][i]NULL[/i][/td][/tr]
[tr][td]3[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]4[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]5[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]1[/td][td]0[/td][td]etc..[/td][/tr]
[tr][td]2[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]6[/td][td]3[/td][td]etc..[/td][/tr]
[/table]

Then messagerelationships contains:
[b][u]messagerelationships[/u][/b]
[table]
[tr][td][b]messageID[/b][/td][td][b]relatedto[/b][/td][/tr]
[tr][td]1[/td][td]0[/td][/tr]
[tr][td]2[/td][td]1[/td][/tr]
[tr][td]2[/td][td]0[/td][/tr]
[tr][td]3[/td][td]1[/td][/tr]
[tr][td]3[/td][td]0[/td][/tr]
[tr][td]4[/td][td]2[/td][/tr]
[tr][td]4[/td][td]1[/td][/tr]
[tr][td]4[/td][td]0[/td][/tr]
[tr][td]5[/td][td]2[/td][/tr]
[tr][td]5[/td][td]1[/td][/tr]
[tr][td]5[/td][td]0[/td][/tr]
[tr][td]6[/td][td]3[/td][/tr]
[tr][td]6[/td][td]1[/td][/tr]
[tr][td]6[/td][td]0[/td][/tr]
[/table]

Ok.. so here is what I want to get when I perform a query to grab every message related to a messageID:
[b][u]RESULT DESIRED[/u][/b]
[table]
[tr]
[td][b]messageID[/b][/td]
[td][b]parentID[/b][/td]
[td][b]etc.[/b][/td]
[/tr]
[tr][td]1[/td][td]0[/td][td]etc..[/td][/tr]
[tr][td]2[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]4[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]5[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]3[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]6[/td][td]3[/td][td]etc..[/td][/tr]
[/table]

but what I get:
[b][u]ACTUAL RESULT[/u][/b]
[table]
[tr]
[td][b]messageID[/b][/td]
[td][b]parentID[/b][/td]
[td][b]etc.[/b][/td]
[/tr]
[tr][td]1[/td][td]0[/td][td]etc..[/td][/tr]
[tr][td]2[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]3[/td][td]1[/td][td]etc..[/td][/tr]
[tr][td]4[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]5[/td][td]2[/td][td]etc..[/td][/tr]
[tr][td]6[/td][td]3[/td][td]etc..[/td][/tr]
[/table]

The query I am using I knew wouldn't give me what I wanted, but I hoped it would be a good start to inspire me to write the correct query.  Here it is:
[code]SELECT * FROM messages
WHERE messageid IN (SELECT messageid
                FROM messagerelationships
                WHERE relatedto = 1)
      OR messageid = 1
ORDER BY parent;[/code]

Any help you can give would be a life-saver!

TIA!
Matt

[fenway]

I'm confused... how is the desired result supposed to be organzied?

[bigmatt19]

The desired result would allow me to produce something like this:
[pre]
Original Message (1)
  Reply to Original (2)
      Reply to 2 (4)
      Reply to 2 (5)
  Reply to Original (3)
      Reply to 3 (6)
[/pre]

And really to be able to nest as deep as I want... dynamically.

Hope that clarifies things a bit.

-Matt

[alpine]

You would be easiest off with a Main-post ID in your table, all sub-posts would relate to the main id, then the parent id will sort further.
That way cou can order them by one table.