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?

Does it give you any error messages? If so, post it here – RisingSun Feb 24 '16 at 22:43 — RisingSun, Feb 24 '16 at 22:43
I've added my answer. I believe this will get you closer. – Kamil Gosciminski Feb 24 '16 at 23:01 — Kamil Gosciminski, Feb 24 '16 at 23:01

Kamil Gosciminski · Accepted Answer · 2016-02-25T13:18:37.753

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.

Hi,thanks for the response and it quite helped me.I just have one more question,how do i group all the locations for the therapist?In your last table output,the days are separated.I would want it to be something like this therapist_name=>Therapist 1,locations=>monday(Location 1,Location 2),wednesday(Location 3),friday(Location 1).And if i add another field in the select return this wont work? — Shile, Feb 25 '16 at 08:26
I did something like this: SELECT (CONCAT(day,'(',GROUP_CONCAT(location_id),')')) as location FROM days_locations WHERE therapist_id=82 GROUP BY day and now it gives me(for example): monday(12,13,14) wednesday(12,13,14) But now i need this to be in one column like this: monday(12,13,14),wednesday(12,13,14) I tried using GROUP_CONCAT on the location but i get an error. — Shile, Feb 25 '16 at 09:48
Your code in comments is unreadable. I've given you a hint on how to do this, but this seems not to be enough for you. I've edited my answer. — Kamil Gosciminski, Feb 25 '16 at 13:18

MySQL group_concat with select inside select

1 Answers1

Linked