CODEWITHSUNDEEP
mysqlsqljoin
V4n1ll4
V4n1ll4

Reputation: 6099

Join multiple columns on MySQL table

I am trying to join multiple columns on a MySQL table. I can't figure out how I can retrieve multiple columns into one single row, so I can display one row on my page.

table: employee_timesheet

id    employee_id    service_area_name    sign_off_1    sign_off_2  sign_off_3    created_date           status       last_updated
1     2              London               Rom           Director    NULL          2015-02-11 17:22:44    Submitted    2015-02-11 17:22:44

table (empty): employees_timesheet_sign_off_team_leaders

id    employee_id    timesheet_id    sign_off_key    team_leader_id

table: employees_timesheet_sign_off_roms

id    employee_id    timesheet_id    rom_id    sign_off_key
1     2              1               4         sign_off_1
2     2              1               5         sign_off_1

table: team_leaders

id    first_name    last_name    email                  reports_to    status    date_added              last_updated
2     Bob           Test         [email protected]        4             Active    2015-02-03 12:50:25     2015-02-03 13:28:56

table: roms

id    first_name    last_name    email                          status    date_added              last_updated
4     Bill          Gates        [email protected]     Active    2015-02-03 13:14:07     2015-02-03 13:28:40
5     Ben           Morris       [email protected]    Active    2015-02-11 17:35:43     NULL

I need to join the above tables to get the following result:

ID: 1
Team Leader: (null)
Rom: Bill Gates,Ben Morris
Date: 2015-02-11 17:22:44

It would be very much appreciated if someone could help with this.

Thanks in advance.

Upvotes: 0

Views: 73

Answers (1)

bluecollarcoder
bluecollarcoder

Reputation: 14399

For your use case, you need to use the GROUP_CONCAT function: http://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/. The query would look something like:

SELECT et.id, CONCAT(l.first_name, ' ', l.last_name) 'team_leader', GROUP_CONCAT(DISTINCT CONCAT(r.first_name, ' ', r.last_name)), et.last_updated
FROM employee_timesheet et
INNER JOIN employees_timesheet_sign_off_roms etr ON et.id = etr.timesheet_id
INNER JOIN roms r ON etr.rom_id = r.id
LEFT JOIN employees_timesheet_sign_off_team_leaders etl ON et.id = etl.timesheet_id
LEFT JOIN team_leaders l ON etl.team_leader_id = l.id
GROUP BY et.id, team_leader, et.last_updated

Upvotes: 2

Related Questions