CODEWITHSUNDEEP
phpmysqldatabasedata-structures
Sean Fisher
Sean Fisher

Reputation: 625

Aggregate Rows of MySQL with a Parent

I have a table named 'posts'. It is in a structure like this:

post_id, post_content, post_parent

The post parent is an int is used to signify if a post is a parent. If it is equal to 0, it is a parent. If it isn't equal to 0, the post_parent is relative to the post_id that is the parent post.

I want a way that I can select the posts in MySQL so that is weighs the posts and orders them according to their parents.

The results should be like this:

Thank you in advance!

Upvotes: 1

Views: 175

Answers (1)

Naftali
Naftali

Reputation: 146310

I have no idea what the table looks like so i am making this up as I go along.

SELECT * from tableName ORDER BY parentID ASC, ChildID ASC

This should get you all parents with their children:

SELECT parent.ID, children.ID 
FROM tableName parent
LEFT JOIN 
      tableName children ON parent.post_id = children.post_parent
ORDER BY parent.post_id

Upvotes: 3

Related Questions