MYSQL使用ORDER By if，订购具有可在两个级别订购的子类别的类别

So i'm looking to fix this following query:

SELECT * FROM `#__gdwproddise_category` 
      ORDER BY IF(`parent_id`, `parent_id`, `ordering`), 
           `parent_id`, `ordering` asc

So the important MYSQL fields are:

`multicat_id` = primary key
`parent_id`
`ordering`

Parent categories parent_id is = 0 the rest is = to the parent's multicat_id

Each unique parent_id (including =0, so parents) have there unique ordering set into the ordering field.

So in the ordering field you will find multiple rows with the value 1, 2, 3 etc. (each value is unique inside of each parent_id

like parent_id=0 will only have 1 row with ordering set to 1.
and parent_id=1 will only have 1 row with ordering set to 1.
etc.

i want to output them by like:

parent (ordering=1)
-child (ordering=1)
-child (ordering=2)
parent (ordering=2)
-child (ordering=1)
etc.

This following query seems to almost work. However the parents are not ordered by there ordering field.. they seem to be ordered by the primary key.

SELECT * FROM `#__gdwproddise_category` 
     ORDER BY IF(`parent_id`, `parent_id`, `multicat_id`), 
         `parent_id`, `ordering` asc

The first query I posted up top was my deduction trying to fix this one but that doesnt work... The first query works better. at least the parents and children are grouped togetter in that first one. this 2nd one un groups the parent from the childrens. the children seem to stick togetter.

I based my query off the example given in one of the answers from the following thread : SQL nested order by?

I found this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

It seem complex to use the "ltg rtg" solution from that article when the query I'm using aLmost does exactly what I want... That article seems to support infinite sub categories... my system only needs 1 level of sub categories.

Since you need to know the ordering of the parent to place a block of child rows at the right place in the result, you will probably have to use a self join.

The following query then seem to give the ordering you want:

SELECT C.* FROM categories AS C
LEFT JOIN categories AS P
ON P.multicat_id = C.parent_id
ORDER BY IF(P.multicat_ID, P.ordering, C.ordering),
         P.ordering, C.ordering;

It can be tested at http://www.sqlfiddle.com/#!2/ba095/1/0