SQL - Category & Subcategory

Question

I have a category and subcategory structure. And I need to list all categories, and then their subcategories... But the listing went wrong. I’m using the codeigniter.

Structure of the Database:

SQL structure:

SELECT c1.*,
       c2.id,
       c2.id_categoria,
       c2.categoria as nome_categoria
  FROM categoria AS c1
  LEFT JOIN categoria AS c2
    ON c2.id_categoria = c1.id

How do I list all the categories and subcategories correctly? Within the same SQL, because I will recover the data by foreach() to display all categories and their subcategories. Today the result is like this:

This should be the result:

The health category should also appear in the example...

Answer 1

If I understand correctly, all main categories have id_category = 0, then:

SELECT * FROM (
    SELECT c1.id,
            c1.categoria as categoria_principal,
            null as sub_categoria
    FROM categoria AS c1

    WHERE c1.id_categoria = 0

    UNION

    SELECT c2.id,
            c1.categoria as categoria_principal,
            c2.categoria as sub_categoria
    FROM categoria AS c1
      INNER JOIN categoria AS c2 ON c2.id_categoria = c1.id

    WHERE c1.id_categoria = 0
) as tbaux
ORDER BY categoria_principal, sub_categoria

First search the main categories, then the main categories plus the subcategories

This is the result:

Streamlined:

SELECT CASE WHEN c2.id IS NULL
                THEN c1.id
                ELSE c2.id
            END AS id,
        c1.categoria as categoria_principal,
        c2.categoria as sub_categoria
FROM categoria AS c1
  LEFT JOIN categoria AS c2 ON c2.id_categoria = c1.id

WHERE c1.id_categoria = 0
ORDER BY categoria_principal, sub_categoria