How can i concatenate multiple MySQL rows into one field?

Question

Using

MySQL

, I can do something like:

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;

The result is:

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        |

but instead I just want

concat

row who i have a

id_category

with

id_parent

Example:

Autre-Demande d'information

Mairie-Demande d'information

Stationnement-Demande d'information

Autre-Réclamation

Familles-Réclamation

TESTDOMAINE-TEST

I've looked for a function on MySQL Doc and it doesn't look like the

CONCAT

or

CONCAT_WS

functions accept result sets, so does anyone here know how to do this?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

How can i concatenate multiple MySQL rows into one field?

Sign up