SQL - Category & Subcategory

Question

SQL - Category & Subcategory

Navigation

#1 by (2 votes)

0

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

Database Structure:

SQLStructure:

SELECTc1.*,c2.id,c2.id_categoria,c2.categoriaasnome_categoriaFROMcategoriaASc1LEFTJOINcategoriaASc2ONc2.id_categoria=c1.id

HowdoIlistallcategoriesandsubcategoriesinthecorrectway?WithinthesameSQL,Iwilldothedatarecoverybyforeach()todisplayallcategoriesandtheirsubcategories.Todaytheresultlookslikethis:

Thisshouldbetheresult:

The health category should also come out in the example ...

mysql sql

asked by anonymous 13.11.2015 / 01:25

1 answer

How to pass an array to a .txt file - MATLAB How can I set the width of each column in a GridView?

score 2 · Accepted Answer

If I understand correctly, all major 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:

Simplified:

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