SQL: Order results by (optional) relationship

I have two tables Post (id, ..., creationdate) and Post_Has_Post(parent_id, child_id).

Some Posts can be part of a parent<->child relation and have a corresponding entry in the Post_Has_Post-Table.

What I'm trying to do is getting a list of posts ordered by their creationdate. Any children of other posts however should be inserted after their parent in the result. Now of course it's easy to order by creationdate, but i'm out of ideas for the second sorting condition. Is there a way to do that at all?

Post                              Post_Has_Post
+------+-------+--------------+   +-----------+----------+
| id   | ...   | creationdate |   | parent_id | child_id |
+------+-------+--------------+   +-----------+----------+
| 1    | ...   | 2010-11-01   |   |  1        |  3       |
| 2    | ...   | 2010-11-02   |   +-开发者_开发百科----------+----------+
| 3    | ...   | 2010-11-03   |
+------+-------+--------------+

I need a result sorted like this:

Post
+------+-------+--------------+   
| id   | ...   | creationdate |  
+------+-------+--------------+  
| 1    | ...   | 2010-11-01   |  
| 3    | ...   | 2010-11-03   | 
| 2    | ...   | 2010-11-02   |
+------+-------+--------------+

Is there a way to solve this through sorting?

Assuming the parent always comes before the child, this should work.

SELECT p.id, ..., creationdate
    FROM Post p
        LEFT JOIN Post_Has_Post php
            ON p.id = php.child_id
    ORDER BY COALESCE(php.parent_id, p.id),
             creationdate

You should not have the table Post_Has_Parent.  Instead look into adding a column to the Post table of "parent_id" and using a relationship between post_id and parent_id and a self join.  When someone posts a reply to a post, just put the parents post id as the parent_id of the new post.  This will allow you to store the relationship in one table.

Depending on the DBMS you can look into using:

Oracle: Connect_be

select seq_num, post_text, parent_id, SYS_CONNECT_BY_PATH(seq_num,
'/') AS PATH, level
 from post
start with seq_num = 1
CONNECT BY NOCYCLE PRIOR seq_num = parent_id;

SQL Server: Common Table Expressions

;with posts as
(

      select seq_num, post_text, parent_id, 0 AS generation,
              CAST(seq_num as varchar(50)) as path
              from recurs_test
              where parent_id is NULL
      UNION ALL
      select e.seq_num, e.post_text, e.parent_id, generation + 1,
              CAST(rtrim(p.path) + '/' +  CAST(e.seq_num as varchar(5)) as
varchar(50)) as path
              from recurs_test e
              inner join posts p
              on e.parent_id = p.seq_num

)

select seq_num, name, parent_id, dir, generation from
managers order by dir;

MYSQL:  You are going to have to look into using some sort of tree traversal algorithm and do a depth first search.  They are all pretty complex and usually involve storing something ( the path, a left and right value, etc ) in the database.  The reason is that MYSQL does not allow any recursive select statements ( correct me if I am wrong so I can redo some code that I have written!! )

You won't have the dates in a single column, but IMHO that's a little confusing anyway. You could choose to show the child thread date or parent thread date in whatever UI is presenting the data:

SELECT Parent.ID, Parent.CreationDate ParentCreationDate,
       Child.CreationDate ChildCreationDate
FROM Post Parent LEFT OUTER JOIN
     Post_Has_Post PHP ON Parent.ID = PHP.Parent_ID LEFT OUTER JOIN
     Post Child ON PHP.Child_ID = Child.ID
ORDER BY Parent.CreationDate, Child.CreationDate;