CODEWITHSUNDEEP
mysqljoingroup-concat
user1633947
user1633947

Reputation: 149

MySQL GROUP_CONCAT and Multiple Lookup Tables

I Have seven tables I am trying to join:

Table   programs
Column  id    program_name    program_description
        1     Self Help       Self Help Description...
        2     Wellness        Wellness Description...
        3     Education       Education Description...
______________________________________________________

Table   county
Column  id    county_name
        1     Stark
        2     Portage
        3     Wayne
_________________________

Table   services
Column  id    service_name    service_description
        1     Counseling      Counseling Description...
        2     Group Therapy   Group Therapy Description...
        3     Evaluation      Evaluation Description...
__________________________________________________________

Table   population
Column  id    population_name
        1     Adults
        2     Children
        3     Youth
_____________________________

Table   program_county
Column  id    program_id    county_id
        1     1             2
        2     1             3
        3     2             1
        4     2             2
_____________________________________

Table   program_service
Column  id    program_id    service_id
        1     1             2
        2     1             3
        3     2             1
        4     2             2
        5     2             3
______________________________________

Table   program_population
Column  id    program_id    population_id
        1     1             3
        2     2             1
        3     2             3
        4     3             1
_________________________________________

I am trying to write a query that would return one row for each program and retrieve the related rows in program_county,program_services, and program_population tables and lookup the names of those services, population and counties display them in one field each. Like:

id   program_name    program_description         Counties           Services                                 Population Served
1    Self Help       Self Help Description...    Portage, Wayne     Group Therapy, Evaluation                Youth
2    Wellness        Wellness Description        Stark, Portage     Counseling, Group Therapy, Evaluation    Adults, Youth

I know I have to use joins and GROUP_CONCAT but I am admittedly very lost.

Upvotes: 0

Views: 281

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

You'll want to use outer joins if you want to see all programs regardless of whether they are associated to counties, services, or populations. Likewise you should use coalesce() around the values you are concatenating to handle NULL values properly.

Something like this should work:

select programs.id, programs.program_name, programs.program_description,
  group_concat(distinct coalesce(county.county_name,'')) as "Counties",
  group_concat(distinct coalesce(services.service_name,'')) as "Services",
  group_concat(distinct coalesce(population.population_name,'')) as "Population Served"
from programs 
  left outer join program_county on program_county.program_id = programs.id
  left outer join county on county.id = program_county.county_id
  left outer join program_service on program_service.program_id = programs.id
  left outer join services on services.id = program_service.service_id
  left outer join program_population on program_population.program_id = programs.id
  left outer join population on population.id = program_population.population_id
group by programs.id, programs.program_name, programs.program_description
order by programs.id

Upvotes: 1

Related Questions