user2311322 user2311322 - 4 months ago 7
SQL Question

MySQL - Retrieve data from database by joining 3 tables and display one column value with comma

I have three tables, named

location
and
Hospital
and
hospital location
. These are the fields and data of both table

Table : location

id | location_name
1 | location1
2 | location2


Table : hospital

id | hospital_name
1 | Hospital1
2 | Hospital2


Table : hospital_location

id | hospital_id | location_id
1 | 1 | 1
2 | 1 | 2


I need to create a query in mysql to display all the data from hospital table. The location_name column has multiple values, separated by a comma.

id | hospital_name | location_name
1 | Hospital1 | location1, location2

Answer

What you need is the GROUP_CONCAT mysql function http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

SELECT h.hospital_name, GROUP_CONCAT(l.location_name) as location_name
FROM hospital h
LEFT JOIN hospital_location hl ON hl.hospital_id = h.id
LEFT JOIN location.l ON hl.location_id = l.id
GROUP BY h.id