20745

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?

Answer1:

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.

<strong>Edit after comments</strong>:

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.

Recommend

  • SQL, same column different rows to same row different columns [closed]
  • How to set a foreign key which is dependent on the relation of other two tables?
  • how to change marker on mouseover and back?
  • Why didn't work my classic asp source code?
  • Save network location as a .txt file (Without using GPS)
  • Plotting points on a map with size depending on category count
  • Cassandra not working with php
  • Timer in Android Service
  • PHP Objects broken, $this is undefined
  • How to write to an Access database on Windows 10
  • Full calendar business hour constrain on day click event
  • Typescript compile on save not working in cordova .jsproject - 2015
  • How to set textwrapping in Button
  • Bing Virtual Earth 7.0 calculate area
  • Instagram API Locations Endpoint
  • How do I import data in one pillar file from another?
  • DRY up gulp tasks
  • OpenSSL SSL_connect: SSL_ERROR_SYSCALL in connection to api.amazonalexa.com:443
  • Wordpress theme Error “Fatal error: Cannot re-assign auto-global variable _POST”
  • how to find the gps location using android emulator?
  • What is the purpose of multidimensional arrays? [closed]
  • Drupal: create a node with employee working hours
  • Parse RRULE to readable text?
  • Implementation of Thread-local storage (TLS) in C/C++ (multithreading)
  • Why does swagger annotations generate api-docs with default path prefix
  • Deserializing this JSON response to C#
  • Name of day in string format with days to furure
  • how to migrate existing records to match new ORM in rails
  • Where can in find the locale objects for d3.js for different countries
  • Browserify and Reactify source maps include full local path names
  • Loopback validation on Properties who's types are other Models
  • How to pass solution folder as parameter in command line arguments (for debug)?
  • How to access culture data in globalize.js V1.0.0
  • Saving Changes After In-App Purchase Has Been Purchased
  • javaw.exe and eclipse startup problems
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?