MySQL - sort 2 columns by joining to one

Question

I have a question.

I am working on a SQL query which will join 2 columns in one table to one column and will sort the result in ascending order (by that column).

So, lets imagine we have a table with auto-incement IDs. In same table we have column parentID (can be 0 or reffering to an existing ID).

ID | parentID | column1 | column2 | number
-------------------------------------------
1  | 0        | data    | data   | 1
2  | 0        | data    | data   | 1
3  | 1        | data    | data   | 1
4  | 2        | data    | data   | 1
5  | 0        | data    | data   | 1
6  | 4        | data    | data   | 1
7  | 1        | data    | data   | 1

And I want to get result where ID and parentID will be joined and sorted together. So the table will look like. (if parentID will be 0, sort it by ID)

ID | parentID | JOINEDCOLUMN | column1 | column2 | number
-------------------------------------------
1  | 0        | 1              | data    | data   | 1
3  | 1        | 1              | data    | data   | 1
7  | 1        | 1              | data    | data   | 1
2  | 0        | 2              | data    | data   | 1
4  | 2        | 2              | data    | data   | 1
6  | 4        | 4              | data    | data   | 1
5  | 0        | 5              | data    | data   | 1

(I do not want data from JOINED COLUMN, but the sorted table as result -> PHP...)

I have a query - which is not working as expected -> returning parentID rows twice if they are not 0.

SELECT 
   id as JOINEDCOLUMN, parentID, column1, column2, column3 
FROM table 
WHERE number = 1 
UNION 
SELECT 
   parentID, parentID, column1, column2, column3 
FROM table 
WHERE number = 1 AND parentID <> 0
ORDER BY `JOINEDCOLUMN` ASC

Answer 1

Use CASE in your ORDER BY

SELECT 
  * 
FROM
  demo  /* add your filter WHERE number = 1 */
ORDER BY 
  CASE
    WHEN parentID = 0 
    THEN ID 
    ELSE parentID 
  END 

Demo, Select