MySQL - How to get multiple rows in a single row

Question

I'm trying to 'CONCAT' the description of multiple rows in a single row.

I have these tables:

tb_employees:

+---------------+---------------+
|  id_employee  |   employee    |
+---------------+---------------+
|     1         | Robert Tomson |
|     2         | Jhonatan Weg  |
|     3         |   Eva Uhte    |
+---------------+---------------+

tb_requirements:

+---------------+-----------------+
|  id_requirem  |    requirem     |
+---------------+-----------------+
|     11        |      Photo      |
|     12        | Criminal Record |
|     13        |  Shooting Test  |
+---------------+-----------------+

tb_details:

+-----------------+---------------------+-------------------------+
|    id_detail    |     id_employee     |       id_requirem       |
+-----------------+---------------------+-------------------------+
|        1        |          1          |          11             |
|        2        |          1          |          12             |
|        3        |          1          |          13             |
|        4        |          2          |          11             |
|        5        |          2          |          13             |
|        6        |          3          |          12             |
|        7        |          3          |          13             |
+-----------------+---------------------+-------------------------+

I have to make a SELECT query to show like this:

+-------------------------+----------------------------------------+
|      employee           |               requirem                 |
+-------------------------+----------------------------------------+
|      Robert Tomson      | Photo - Criminal Record - Shooting Test|
|       Jhonatan Weg      |       Photo - Shooting Test            |
|         Eva Uhte        |     Criminal Record - Shooting Test    |
+-------------------------+----------------------------------------+ 

To be honest, i really don't know how :S

Any ideas? Thank you for answer.

Answer 1

You need to use group_concat Try the below :

SELECT  group_concat(distinct(e.employee) separator ', ') , group_concat(r.requirem separator ', ') 
FROM TB_DETAILS d
          JOIN TB_EMPLOYEES e ON d.id_employee = e.id_employee
          JOIN tb_requirements r ON d.id_requirem = r.id_requirem


| GROUP_CONCAT(DISTINCT(E.EMPLOYEE) SEPARATOR ', ') |  GROUP_CONCAT(R.REQUIREM SEPARATOR ', ') |
|---------------------------------------------------|------------------------------------------|
|   Robert Tomson, Jhonatan Weg, Eva Uhte           | Photo, Criminal Record, Shooting Test,   |
|                                                   | Photo, Shooting Test, Criminal Record,   | 
|                                                   |  Shooting Test                           | 

Answer 2

Use GROUP_CONCAT()

SELECT e.employee, GROUP_CONCAT(r.requirem ORDER  BY r.requirem SEPARATOR ' - ') requirements
  FROM tb_employees e LEFT JOIN tb_details d
    ON e.id_employee = d.id_employee JOIN tb_requirements r
    ON d.id_requirem = r.id_requirem
 GROUP BY e.id_employee, e.employee

Output:

|      EMPLOYEE |                            REQUIREMENTS |
|---------------|-----------------------------------------|
| Robert Tomson | Criminal Record - Photo - Shooting Test |
|  Jhonatan Weg |                   Photo - Shooting Test |
|      Eva Uhte |         Criminal Record - Shooting Test |

Here is SQLFiddle demo

Answer 3

Edit:

You should use GROUP_CONCAT and GROUP BY the requirem field.