MySQL pivot row for multiple columns

MySQL

I have the following table.

Same user can be associated with multiple org and the contact details should eb separtae by org.
User can have multiple roles.
User can have multiple working days.

Tables

Sample Data

1. user
---------------------
| user_id | email.  |
---------------------
| 1 | test@test.com |
---------------------
  1. user_contact
---------------------------------------------
| user_id | org_id | first_name | last_name |
---------------------------------------------
| 1       | 1      | TestFName  | TestLname |
---------------------------------------------
| 2       | 1      | TestFName2 | TestLname2 |
---------------------------------------------
  1. user_role
--------------------
|role_cd| role_name|
--------------------
| AD     | ADMIN   |
--------------------
| MN     | MANAGER |
--------------------
  1. user_role_mapping
----------------------------
|user_id| role_cd | org_id |
----------------------------
| 1     | AD      |  1     |
----------------------------
| 1     | MN      |  1     |
----------------------------
| 2     | AD      |  1     |
----------------------------
  1. user_working_hours
----------------------------------------------
|user_id| org_id | day  | st_time | end_time |
----------------------------------------------
| 1     | 1      | SUN  | 8:00    |  4:00    |
----------------------------------------------
| 1     | 1      | MON  | 8:30    |  3:00    |
----------------------------------------------
| 2     | 1      | MON  | 8:00    |  4:00    |
----------------------------------------------
| 2     | 1      | TUE  | 8:30    |  3:00    |
----------------------------------------------

I need a query which will give me the data in the following format.

--------------------------------------------------------------
|user_id| org_id | first_name  | last_name | roles | days    |
--------------------------------------------------------------
| 1     | 1      | TestFName   | TestLname |  AD,MN| SUN, MON|
--------------------------------------------------------------
| 2     | 1      | TestFName2  | TestLname2|  AD.  | MON, TUE|
--------------------------------------------------------------

Note:-
User contact may or may not have entry for a user id.

Query I tried,

select u.user_id, u.email, Group_concat(ur.role_name) roles
  from user u 
  left join user_role_mapping urm on urm.user_id = u.user_id left join user_role ur on ur.role_cd = urm.role_cd 
  where urm.org_id = 1 group by u.user_id;

This gives the concatenated role, but adding user_contact gives error.

Fiddle Link:-

https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=71bfdf305e06f235d4c14810bf9c2283

Best Answer

As the error message says.

When you use GROUP BYand ONLY_FULL_GROUP_BY is enabled(which should always be:

Then all Columns of the SELECT have to be in the GROUP BY or has to have a aggregation function

So your query should be

SELECT u.user_id, MIN(uc.first_name), MIN(uc.last_name), GROUP_CONCAT(ur.role_name) roles
 FROM user u 
  LEFT JOIN user_contact uc ON u.user_id = uc.user_id
  LEFT JOIN user_role_mapping urm ON urm.user_id = u.user_id 
  LEFT JOION user_role ur ON ur.role_cd = urm.role_cd 
  WHERE urm.org_id = 1 
 GROUP BY u.user_id;
Related Question