Mysql - group, but show all rows of a column

Question

I want to group the fields, but I want to show the entire row of the column named remain :

here is the current output without grouping:

And the conclusion with the group:

And I want It when I group:

Here is the mysql query:

 SELECT staff.name, staff.designation_id_designation, staff.status_id_status, Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) AS remain FROM staff INNER JOIN leave_remain ON staff.id_staff = leave_remain.staff_id_staff GROUP BY staff.name 

Answer 1

use GROUP_CONCAT

 SELECT staff.name, staff.designation_id_designation, staff.status_id_status, GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days)) AS remain FROM staff INNER JOIN leave_remain ON staff.id_staff = leave_remain.staff_id_staff GROUP BY staff.name, staff.designation_id_designation, staff.status_id_status 

• SQLFiddle Demo

other Link (s)

• MySQL GROUP_CONCAT

Answer 2

Try the following:

 SELECT staff.name, staff.designation_id_designation, staff.status_id_status, GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) SEPARATOR ' ') AS remain FROM staff INNER JOIN leave_remain ON staff.id_staff = leave_remain.staff_id_staff GROUP BY staff.designation_id_designation, staff.name, staff.status_id_status; 

Answer 3

To do this, we need to use group_contact separately from '..

 SELECT staff.name, staff.designation_id_designation, staff.status_id_status, GROUP_CONCAT(Concat_Ws('=', leave_remain.leave_type_id_leave_type, leave_remain.days) SEPARATOR ' ') AS remain FROM staff INNER JOIN leave_remain ON staff.id_staff = leave_remain.staff_id_staff GROUP BY staff.designation_id_designation, staff.name, staff.status_id_status;