MySQL group_concat with select inside select

Question

I have a glitch which i cannot solve,let me elaborate...

These are my MySQL tables...

Therapists table

id  therapist_name
1   Therapist 1
2   Therapist 2 

Location table

+-----+------------+--+
| id  |    name    |  |
+-----+------------+--+
|  1  | Location 1 |  |
|  2  | Location 2 |  |
|  3  | Location 3 |  |
+-----+------------+--+

Days_location table

+-----+-----------+--------------+-------------+--+
| id  |    day    | therapist_id | location_id |  |
+-----+-----------+--------------+-------------+--+
|  1  | monday    |            1 |           1 |  |
|  2  | monday    |            1 |           2 |  |
|   3 | wednesday |            1 |           3 |  |
|   4 | wednesday |            2 |           1 |  |
|   5 | tuesday   |            2 |           2 |  |
|   6 | friday    |            2 |           1 |  |
|   7 | friday    |            2 |           2 |  |
|   8 | friday    |            1 |           1 |  |
+-----+-----------+--------------+-------------+--+

Now i want to get every therapist with locations for every day,for example something like this:

therapist_name=>Therapist 1,day_locations=>monday(Location1,Location2),friday(Location1)

I need it to be as a select variable,this was my query but i got stuck there:

SELECT t.*,GROUP_CONCAT(
    SELECT CONCAT(dl2.day,GROUP_CONCAT(dl2.location_id)) as concated 
    FROM days_location dl2 
    WHERE therapist_id=85 
    GROUP BY dl2.day
) as day_location 
FROM therapists t  
LEFT JOIN days_location dl 
ON dl.therapist_id=t.id

This of course doesn't work,what am i doing wrong...should i try a different approach or make my tables different?

Answer 1

I believe this is what you're looking for, or could get you started:

SELECT
    t.therapist_name,
    dl.day,
    GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
FROM
    therapists t 
    LEFT JOIN days_location dl ON dl.therapist_id = t.id
    LEFT JOIN location l ON dl.location_id = l.id
GROUP BY t.therapist_name, dl.day

For therapists.id = 1 this should give you results:

+----------------+-----------+-----------------------+
| therapist_name |    day    |       locations       |
+----------------+-----------+-----------------------+
| Therapist 1    | monday    | Location 1,Location 2 |
| Therapist 1    | wednesday | Location 3            |
| Therapist 1    | friday    | Location 1            |
+----------------+-----------+-----------------------+

If you need to concatenate day with locations column then use a simple CONCAT():

SELECT
    therapist_name,
    CONCAT(day, '(', locations, ')') AS locations
FROM (  
    SELECT
        t.therapist_name,
        dl.day,
        GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
    FROM
        therapists t 
        LEFT JOIN days_location dl ON dl.therapist_id = t.id
        LEFT JOIN location l ON dl.location_id = l.id
    GROUP BY t.therapist_name, dl.day
    ) t
GROUP BY therapist_name, locations

Output should look like:

+----------------+-------------------------------+
| therapist_name |           locations           |
+----------------+-------------------------------+
| Therapist 1    | monday(Location 1,Location 2) |
| Therapist 1    | wednesday(Location 3)         |
| Therapist 1    | friday(Location 1)            |
+----------------+-------------------------------+

If you need to group it all into one row for each therapist, then you could GROUP_CONCAT() again.

Edit after comments:

SELECT
    therapist_name,
    GROUP_CONCAT( CONCAT(day, '(', locations, ')') SEPARATOR ',' ) AS locations
FROM (      
    SELECT
            t.therapist_name,
            dl.day,
            GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
    FROM
            therapists t 
            LEFT JOIN days_location dl ON dl.therapist_id = t.id
            LEFT JOIN location l ON dl.location_id = l.id
    GROUP BY t.therapist_name, dl.day
    ) t
GROUP BY therapist_name

I haven't tested the code so there may be some minor mistakes to tweak. No way of testing it atm.