CODEWITHSUNDEEP
mysqlsql
Brian Patterson
Brian Patterson

Reputation: 1625

MySQL - Group Query Values in Same Table

Ok, so I have a table with TaskID and ChildOf as fields... so ...

TaskID is Unique and Auto-Increment 

If ChildOf == 0, then it has no Parent (top tier)...

If ChildOf != 0, then it is assigned TaskId of its Parent

Question: How to form a query to give the results like this ...

Parent Task
 Child Task
 Child Task

Parent Task
 Child Task

Etc.. etc...

Would it be easier if I use two tables? I'm going to have a lot of items in this table so I want to use as few queries (loops) as possible.

Any help is greatly appreciated.

Thanks

Upvotes: 1

Views: 409

Answers (3)

pratik garg
pratik garg

Reputation: 3342

I think there is one way by which you have to just do soting without using join....

this is as follow -

select * from table_name order by decode(ChildOf,0,TaskID,ChildOf),ChildOf

this will give desired output...

means parent node and then respective child node... and then second parent node and so on..

one thing I don't know whether docode function runs on my sql as well or not..

if not then you can use following query -

select * from table_name order by case when ChildOf=0 then TaskID else ChildOf end,ChildOf

Upvotes: 1

Andriy M
Andriy M

Reputation: 77677

Based on how your sample output is formatted, it seems like you only have two levels of hierarchy, i.e. every item is either a parent or a child.

If that is so, you could do something like this:

SELECT t.*
FROM atable t
  LEFT JOIN atable p ON t.ChildOf = p.TaskId
ORDER BY
  COALESCE(p.TaskId, t.TaskId),
  CASE WHEN p.TaskId IS NOT NULL THEN t.TaskId ELSE 0 END

This will group parents together with their children, sorting the former before the latter.

Upvotes: 0

jworrin
jworrin

Reputation: 835

I would recommend using recursion for this. Check out http://www.sitepoint.com/hierarchical-data-database/ for a good breakdown on how to do this.

If you are against using recursion, then you can look at using a left and a right value to determine where the and using a tree traversal algorithm to retrieve your data.

Just let me know if you do not want to use recursion and I can explain how to do it the other ways ( they are a lot more complex and I did not want to type it out if you were just going to use recursion because it would take about an hour or so to type out!! )

Upvotes: 0

Related Questions