Using
MySQL
SELECT c.id_category, c.id_category_type, c.label, c.id_parent
FROM category c
WHERE c.id_category_type < 3
ORDER BY c.id_category_type;
id_category |id_category_type |label |id_parent |
------------|-----------------|----------------------|----------|
1 |1 |Demande d'information |-1 |
2 |1 |Réclamation |-1 |
18 |1 |TEST |-1 |
3 |2 |Autre |1 |
4 |2 |Mairie |1 |
5 |2 |Stationnement |1 |
6 |2 |Autre |2 |
7 |2 |Familles |2 |
19 |2 |TESTDOMAINE |18 |
concat
id_category
id_parent
Autre-Demande d'information
Mairie-Demande d'information
Stationnement-Demande d'information
Autre-Réclamation
Familles-Réclamation
TESTDOMAINE-TEST
CONCAT
CONCAT_WS
Test this:
SELECT category.id_category, category.id_category_type,
CONCAT(category.label, '-', parents.label), category.id_parent FROM category INNER JOIN
(SELECT id_category AS id, label FROM category WHERE id_parent = -1) AS parents
ON parents.id = category.id_parent WHERE category.id_category_type < 3
ORDER BY category.id_category_type;
Your data mixes "parents rows" and "child rows": when a register has id_parent = -1, then i consider is a parent row, and when id_parent <> -1 is child row.
You are requesting a result column joining "label" column from "childs rows" and "parents rows", then its necesary build a "parent table", that is achieved with this:
(SELECT id_category AS id, label FROM category WHERE id_parent = -1) AS parent
then you can use "parent" table like any other table and build your join query